Excel Macro to perform multi column sort

Question

I'm trying to create a macro in Excel that will sort spreadsheets by columns A, C and F. I found information online to get me started. Below is the code. The challenge I'm having is, the spreadsheet will contain a different number of rows each day. (The columns will always be the same but the row count will change). The below script will sort my data as long as the spreadsheet does not contain more than 9999 rows (that includes column headings). If I have 10,000 or more rows, the macro fails.

How do I update the below code to allow it to run regardless of the number of rows? Any help you can provide would be appreciated. Thank you

Sub Multi_Sort()
'
' Multi_Sort Macro
'
Dim lngLast As Long
lngLast = Range("A" & Rows.Count).End(xlUp).Row

With Worksheets("Sheet1").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("A1:A1" & lngLast), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("C1:C1" & lngLast), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("F1:F1" & lngLast), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange Range("A1:F" & lngLast)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

jblood94 jblood94 · Accepted Answer · 2018-04-30T15:26:47

I can't replicate your error in Excel 2013, but I'm guessing the extra "1" in your range assignments is the problem (i.e., change =Range("A1:A1" & lngLast) to =Range("A1:A" & lngLast), etc.).

Also, a good practice would be to be explicit in your range calls (include the worksheet).

Sub Multi_Sort()

Dim lngLast As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1")
lngLast = ws.Range("A" & .Rows.Count).End(xlUp).Row

With .Sort
    .SortFields.Clear
    .SortFields.Add Key:=ws.Range("A1:A" & lngLast), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=ws.Range("C1:C" & lngLast), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=ws.Range("F1:F" & lngLast), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange ws.Range("A1:F" & lngLast)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

Excel Macro to perform multi column sort

1 Answers