Solved

VBA WorksheetChange Event To sort Data based on a specific column

Posted on 2015-01-15
4
80 Views
Last Modified: 2015-01-15
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
Comment
Question by:Flora
  • 2
  • 2
4 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
 
LVL 5

Author Comment

by:Flora
Comment Utility
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
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
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
 
LVL 5

Author Closing Comment

by:Flora
Comment Utility
you are my hero :-)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now