CODEWITHSUNDEEP
excelvbasorting
Natalia Fontiveros
Natalia Fontiveros

Reputation: 115

sort multiple columns excel VBA

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?

Upvotes: 3

Views: 72101

Answers (5)

Ravinder Dangi
Ravinder Dangi

Reputation: 1

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

Upvotes: 0

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4233

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

Upvotes: 0

A7V
A7V

Reputation: 1

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

Upvotes: 0

jamheadart
jamheadart

Reputation: 5313

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.

Upvotes: 10

user4039065
user4039065

Reputation:

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

Upvotes: 2

Related Questions