sort multiple columns excel VBA
Just reiterating Jeeped's answer here but with a slightly different take:
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
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
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
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.
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 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 .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
jamheadart over 4 yearsWhat value does
mylinehave when you're calling this?
Admin over 4 yearsWas here a specific reason for passing the myLine (aka lastRow) in as a string? Is it needed at all?