VBA Excel Variable Sorting on Multiple Keys/Orders

20,226
Sub sdad()
    wsLast_Row = Cells(Rows.Count, 2).End(xlUp).Row
    With ActiveWorkbook.ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A3:A" & wsLast_Row), Order:=xlAscending
        .SortFields.Add Key:=Range("B3:B" & wsLast_Row), Order:=xlAscending
        .SortFields.Add Key:=Range("C3:C" & wsLast_Row), Order:=xlAscending
        .SortFields.Add Key:=Range("D3:D" & wsLast_Row), Order:=xlAscending
        .SetRange Range("A3:BZ" & wsLast_Row)
        .Header = xlNo
        .Apply
    End With
End Sub
Share:
20,226
lindqmx1
Author by

lindqmx1

Updated on September 19, 2020

Comments

  • lindqmx1
    lindqmx1 over 3 years

    How can I sort on multiple columns (keys/orders) at the same time? When I run my below code it re-sorts the data when executing each line instead of one sort with multiple columns in the sort. Changing the 'key' or 'order' number from 1 to 2, etc. does not help. Thanks.

    wsLast_Row = Cells(Rows.Count, 2).End(xlUp).Row
    Range("A3:BZ" & wsLast_Row).Sort key1:=Range("A3:A" & wsLast_Row), _
       order1:=xlAscending, Header:=xlNo
    Range("A3:BZ" & wsLast_Row).Sort key1:=Range("B3:B" & wsLast_Row), _
       order1:=xlAscending, Header:=xlNo
    Range("A3:BZ" & wsLast_Row).Sort key1:=Range("C3:C" & wsLast_Row), _
       order1:=xlAscending, Header:=xlNo
    Range("A3:BZ" & wsLast_Row).Sort key1:=Range("D3:D" & wsLast_Row), _
       order1:=xlAscending, Header:=xlNo
    Range("A3:BZ" & wsLast_Row).Sort key1:=Range("F3:F" & wsLast_Row), _
       order1:=xlAscending, Header:=xlNo