dbmagnus
09-21-2010, 12:49 PM
I am new to VB and need help. I’ve searched the forum(s) and can’t quite find the code I need. I'm using Excel 2010. My problem is simple, really. I have a data validation list. When I select a value from the list (cell “B1”) , I have two columns of data with VLOOKUPS that reference the drop-down value (the data columns are range A4:C33). All I want to happen is each time I select a value from the data validation list, I want to sort the range A4:C33 by column B ,descending.
I copied the sort macro as:
Sub Sort1()
'
' Sort1 Macro
'
Range("A4:C34").Select
ActiveWorkbook.Worksheets("UU Graph").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("UU Graph").Sort.SortFields.Add Key:=Range("B5:B34") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("UU Graph").Sort
.SetRange Range("A4:C34")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C1").Select
End Sub
I know, it's probably crude for a simple sort, but it works. What I haven't had success with is finding code that says when the value of cell B1 changes, to re-sort the range (i.e. run the macro).
Thanks in advance for your help!
I copied the sort macro as:
Sub Sort1()
'
' Sort1 Macro
'
Range("A4:C34").Select
ActiveWorkbook.Worksheets("UU Graph").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("UU Graph").Sort.SortFields.Add Key:=Range("B5:B34") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("UU Graph").Sort
.SetRange Range("A4:C34")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C1").Select
End Sub
I know, it's probably crude for a simple sort, but it works. What I haven't had success with is finding code that says when the value of cell B1 changes, to re-sort the range (i.e. run the macro).
Thanks in advance for your help!