sort multiple columns excel VBA


Solution 1

Just reiterating Jeeped's answer here but with a slightly different take:

1) myline should really be defined as a Long or Integer

2) The ranges declared with Key:=Range("A1") should be defined as the same worksheet

3) The keys for D and J are outside of the .setRange which again, should be defined as being on the same worksheet also

I've stuck with your same code but added the ws worksheet definition to all ranges, and changed your set range to include up to column J

Sub SortMultipleColumns(myline As Long)

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Result-Inactive")

    With ws.Sort
        .SortFields.Add Key:=ws.Range("A1"), Order:=xlAscending
        .SortFields.Add Key:=ws.Range("D1"), Order:=xlAscending
        .SortFields.Add Key:=ws.Range("J1"), Order:=xlAscending
        .SetRange ws.Range("A1:J" & myline)
        .Header = xlYes
    End With

End Sub

I'm assuming myline is there because sometimes you only want to sort the top set of a range of data. I also added a line to clear all the sortfields, just in case you run many different sorters on this sheet.

Solution 2

You are trying to include columns D and J as secondary sorting criteria but excluding them from the sorted range. Additionally, The Range("xn") do not necessarily belong to the Result-Inactive worksheet without being syntaxed as .Range("xn").Try the alternate VBA sort instead of the method produced by the 'macro' recorder.

Sub SortMultipleColumns()

    With Worksheets("Result-Inactive")
        with .cells(1, "A").currentregion
            .Cells.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
                        Key2:=.Range("D1"), Order2:=xlAscending, _
                        Key3:=.Range("J1"), Order3:=xlAscending, _
                        Orientation:=xlTopToBottom, Header:=xlYes
        end with
    End With

End Sub

Related videos on Youtube

Natalia Fontiveros
Author by

Natalia Fontiveros

Updated on November 28, 2020


  • Natalia Fontiveros
    Natalia Fontiveros over 2 years

    this is my code

    Sub SortMultipleColumns(myline As String)
        With Worksheets("Result-Inactive").Sort
            .SortFields.Add Key:=Range("A1"), Order:=xlAscending
            .SortFields.Add Key:=Range("D1"), Order:=xlAscending
            .SortFields.Add Key:=Range("J1"), Order:=xlAscending
            .SetRange Range("A1:C" & myline)
            .Header = xlYes
        End With
    End Sub

    I get the following error and I dont undertsand why "Run time error '1004' the sort reference is not valid. MAke sure that it is within the daya you want to sort, and then first Sort by Box isnt the same or blank. when I click debug. The .apply gets higlighted

    any suggestions?

    • jamheadart
      jamheadart over 4 years
      What value does myline have when you're calling this?
    • Admin
      Admin over 4 years
      Was here a specific reason for passing the myLine (aka lastRow) in as a string? Is it needed at all?