asked on Decoding a line of VBA DataSort
WS.Range("B3:C" & MaxRow).Sort Key1:=WS.Range("C3"), order1:=xlAscending, MatchCase:=False, Header:=xlYes
Does WS need to have a value?
I am trying to sort a dataset by column F, that starts at row 2 with headers and I wanted to use this for a dynamic range.
Trying to sort Largest to Smallest
This is what the recorder recorded.
Ln 6 will not work, in a dynamic dataset. Any ideas?
Does WS need to have a value?
I am trying to sort a dataset by column F, that starts at row 2 with headers and I wanted to use this for a dynamic range.
Trying to sort Largest to Smallest
This is what the recorder recorded.
ActiveWorkbook.Worksheets("CountSummary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CountSummary").Sort.SortFields.Add Key:=Range( _
"F2:F89"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("CountSummary").Sort
.SetRange Range("A1:Y89")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Ln 6 will not work, in a dynamic dataset. Any ideas?
VBAMicrosoft ExcelMicrosoft Office
Last Comment
Shums Faruk
ASKER
Looks like this is very close to working.
but it did not sort by Col F, looks like col A??
Dim Ws As Worksheet
Dim LR As Long
'Application.ScreenUpdating = True
Set Ws = ActiveSheet
LR = Ws.Range("B" & Rows.Count).End(xlUp).Row
Ws.Range("A1:P" & LR).Sort Key1:=Ws.Range("A1"), order1:=xlAscending, MatchCase:=False, Header:=xlYes
but it did not sort by Col F, looks like col A??
Because you are specifying Key1:=Ws.Range("A1")
Try below:
Try below:
Sub SortDataOnMultipleColumns()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("CountSummary")
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Sort.SortFields.Clear
Ws.Sort.SortFields.Add Key:=Range("F1:F" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Ws.Sort
.SetRange Range("A1:P" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub
ASKER
Works... any idea how to reduce it down to the one line version?
My friend, you seems like to play with fire. LOL
Not sure but try below:
Ws.Columns("A:P").Sort key1:=Range("F:F"), order1:=xlAscending, Header:=xlYes
ASKER
I am the FireStarter with the Eternal overflow cup. :-) Your code works. I was just trying to understand how that one line of code worked.
ASKER
One line of code worked.. Final product looked like this:
I was unsure about the key1 and the order1 ??? but it did not seem to matter, in this case.
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("CountSummary")
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Sort.SortFields.Clear
Ws.Columns("A:P").Sort key1:=Range("F:F"), order1:=xlDescending, Header:=xlYes
I was unsure about the key1 and the order1 ??? but it did not seem to matter, in this case.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
EXCELent!! Thanks for the help.
You're Welcome RWayneH! Glad I was able to help :)
Please post your full code, its difficult to understand with single line, anyway you can try below code:
Open in new window