sort multiple columns excel VBA

Question

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
        .Apply
    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?

Was here a specific reason for passing the myLine (aka lastRow) in as a string? Is it needed at all? — user4039065
– user4039065, Commented Oct 3, 2018 at 4:47

jamheadart · Accepted Answer · 2018-10-03 04:55:20Z

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.Clear
        .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
        .Apply
    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.

user4039065 · Accepted Answer · 2018-10-03 04:46:50Z

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

tyg · Accepted Answer · 2020-11-27 15:12:27Z

0

Try this:

Sub SortingColumnsInRange
    ' Selecting range
    Range("A:X").Select

    ' Sort column C, after column N and after column P
    Selection.Columns.Sort key1:=Columns("C"), Order1:=xlAscending, Key2:=Columns("N"), Order2:=xlAscending, key3:=Columns("P"), Order3:=xlAscending, Header:=xlYes
End Sub

edited Nov 27, 2020 at 15:12

tyg

20.8k5 gold badges46 silver badges59 bronze badges

answered Nov 27, 2020 at 12:46

A7V

1

Comments

ListenSoftware Louise Ai Agent · Accepted Answer · 2022-12-09 22:54:09Z

Convert your data into a table then using vba, find my table using the worksheet using listObjects. I then find the index for column 1,2, and 3. Then I add the SortFields using the table range and the column index.

this didn't work:

  Set data_table = MyWorksheet.ListObjects("Tbl_Name")
   column1_index = data_table.ListColumns("Column 1").index
   column2_index = data_table.ListColumns("Column 2").index
   column3_index = data_table.ListColumns("Column 3").index
        
   With data_table.Sort
            .SortFields.Add Key:=data_table.Range(column1_index), Order:=xlAscending
            .SortFields.Add Key:=data_table.Range(column2_index), Order:=xlAscending
            .SortFields.Add Key:=data_table.Range(column3_index), Order:=xlDescending
            .Header = xlYes
            .Apply
        End With

this worked:

 ProbabilitiesWorksheet.Range("B5").Sort Key1:=Range("F5"), Order1:=xlAscending, Header:=xlYes
        ProbabilitiesWorksheet.Range("B5").Sort Key1:=Range("D5"), Order1:=xlDescending, Header:=xlYes
        ProbabilitiesWorksheet.Range("B5").Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlYes

Spencer Barnes · Accepted Answer · 2022-12-15 21:34:46Z

0

Sub SortingColumnsInRange
    ' Selecting range
    Range("A:X").Select

    ' Sort column C, after column N and after column P
    Selection.Columns.Sort key1:=Columns("C"), Order1:=xlAscending, Key2:=Columns("N"), Order2:=xlAscending, key3:=Columns("P"), Order3:=xlAscending, Header:=xlYes
End Sub

edited Dec 15, 2022 at 21:34

Spencer Barnes

2,8851 gold badge10 silver badges30 bronze badges

answered Dec 8, 2022 at 17:27

Ravinder Dangi

11 bronze badge

Collectives™ on Stack Overflow

sort multiple columns excel VBA

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related