Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

VBA WorksheetChange Event To sort Data based on a specific column

Hello,

please see attached file.
i need help with VBA on worksheet changeevent that whenever any new data or any change made on sheet1, the VBA auto sorts in ascending order one column that contains the header label "SP Name"



thanks.
EE.xlsm
0
Flora
Asked:
Flora
  • 2
  • 2
1 Solution
 
Rgonzo1971Commented:
HI,

pls try

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Range(Range("A1"), Range("K" & Cells.Rows.Count).End(xlUp)).Sort _
            key1:=Range("D1"), order1:=xlAscending, Header:=xlYes
End Sub

Open in new window

Regards
0
 
FloraAuthor Commented:
Thanks very much Rgonzo1971

if you are away from your machine, i can wait, the above codes works, but there are two issues here.

A) my data varies time to time, so my end of data to the left is not column K, so it could expand to further columns.
is there any possibility that to use somthign that works dynamic instead of fixed column K?

B) if the sort column could also be set dynamically. for example  trigger sort only in a column where its header input name is "SP Name"     so for example. if my next data varies and the SP Name is in Column C then the above code will not work unless i manually change it key1:=Range("C1"),   so would it be possible to put a seach on key1:= search"Sp Name", in row 1 ? somthing like this?
0
 
Rgonzo1971Commented:
Then try

Private Sub Worksheet_Change(ByVal Target As Range)
    lngCol = Range("1:1").Find("SP Name").Column
    ActiveSheet.Range(Range("A1"), Range("K" & Cells.Rows.Count).End(xlUp)).Sort _
            key1:=Cells(1, lngCol), order1:=xlAscending, Header:=xlYes

End Sub

Open in new window

Regards
0
 
FloraAuthor Commented:
you are my hero :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now