CODEWITHSUNDEEP
vbaexcel
sbagnato
sbagnato

Reputation: 496

Excel VBA - Correct way to sort on two unnamed columns

I want to do an expanded sort on 2 columns (the columns never change). The code should first sort column D, and then sort column E. While I can use the macro recorder to accomplish this, it produces about 20 lines of code, when in reality, I know it can be done in about 5 or 6.

The below piece of code works for one column, although I think even it can be cleaned up some.

Worksheets("CSAT Details").Sort.SortFields.Add Key:=Range("E1"), SortOn:=xlSortOnValues, Order:=xlAscending
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("CSAT Details").Sort
    .SetRange Range("A2:F" & lastRow)
    .Orientation = xlTopToBottom
    .Apply
End With

I have created the below code to attempt to sort on the 2 columns, but it does not yet work, giving a

448 named argument not found error

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("CSAT Details").Sort.SortFields.Add _
    Key1:=Range("D1"), SortOn1:=xlSortOnValues, Order1:=xlAscending, _
    Key2:=Range("E1"), SortOn2:=xlSortOnValues, Order2:=xlAscending
With Worksheets("CSAT Details").Sort
    .SetRange Range("A2:F" & lastRow)
    .Orientation = xlTopToBottom
    .Apply
End With

I am not sure what I am missing.

Upvotes: 0

Views: 251

Answers (2)

Xabier
Xabier

Reputation: 7735

Something like the following (simply specify your range before running):

Sub foo()
Range("A1:G6").Sort Key1:=Range("E1"), Order1:=xlAscending, Key2:=Range("D1") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
End Sub

Upvotes: 2

tigeravatar
tigeravatar

Reputation: 26650

Something like this should work for you:

Sub tgr()

    Dim ws As Worksheet
    Dim LastRow As Long

    Set ws = ActiveWorkbook.Sheets("CSAT Details")
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    With ws.Range("D1:E" & LastRow)
        .Sort .Resize(, 1), xlAscending, .Offset(, 1).Resize(, 1), , xlAscending, Header:=xlYes
    End With

End Sub

Upvotes: 1

Related Questions