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.

       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

Open in new window

Ln 6 will not work, in a dynamic dataset. Any ideas?

Shums Faruk

Hi Rwayneh,

Please post your full code, its difficult to understand with single line, anyway you can try below code:

Sub SortData()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = True
Set Ws = ActiveSheet
LR = Ws.Range("B" & Rows.Count).End(xlUp).Row

Ws.Sort.SortFields.Clear
    Ws.Sort.SortFields.Add Key:=Range("F2:F" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Ws.Sort
        .SetRange Range("A2:F" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Application.ScreenUpdating = True
End Sub

Open in new window

RWayneH

ASKER

Looks like this is very close to working.

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

Open in new window

but it did not sort by Col F, looks like col A??

Shums Faruk

Because you are specifying Key1:=Ws.Range("A1")
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

Open in new window

RWayneH

ASKER

Works... any idea how to reduce it down to the one line version?

Shums Faruk

My friend, you seems like to play with fire. LOL

Shums Faruk

Not sure but try below:

Ws.Columns("A:P").Sort key1:=Range("F:F"), order1:=xlAscending, Header:=xlYes

Open in new window

RWayneH

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.

RWayneH

ASKER

One line of code worked.. Final product looked like this:

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

Open in new window

I was unsure about the key1 and the order1 ??? but it did not seem to matter, in this case.

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RWayneH

ASKER

EXCELent!! Thanks for the help.

Shums Faruk

You're Welcome RWayneH! Glad I was able to help :)