VBA Excel Variable Sorting on Multiple Keys/Orders

Question

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

Andras · Accepted Answer · 2019-09-19 03:10:50Z

8

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

edited Sep 19, 2019 at 3:10

Andras

4032 silver badges15 bronze badges

answered Jun 23, 2016 at 17:51

Abe Gold

2,38718 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

lindqmx1 Over a year ago

I copied your code into my sub and received an error box reading 'Compile error: Named argument not found' 'key4:=' is highlighted.

Abe Gold Over a year ago

Never knew that VBA was limited to 3 keys. See revised answer

lindqmx1 Over a year ago

Works perfectly as intended, ty @Abe Gold

Alex M Over a year ago

I ran into the 3-key limitation today and this worked a treat. Thank you Abe Gold!

Collectives™ on Stack Overflow

VBA Excel Variable Sorting on Multiple Keys/Orders

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related