Macro - Multi Level sort by column name

Question

I download a set of data from a program that i put into excel. From here, i would like to sort this data in 2 levels. I recorded a macro for this, which serves the basic purpose.

The issue is that I would like the macro to search for the column names (As it's not necessary that the columns would always be in B & F), and sort accordingly. Hence, i would like the code to be such that it looks for the columns titled "Asset Name" & "Action", and sort those.

Here's the macro code that i recorded: (I put in the range of 50000, as i wanted it to be dynamic for no. of rows, instead of static, which recording macros does)

Cells.Select
ActiveSheet.sort.SortFields.Clear
ActiveSheet.sort.SortFields.Add Key:=Range("B2:B50000") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.sort.SortFields.Add Key:=Range("F2:F50000") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.sort
    .SetRange Range("A1:H50000")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

To incorporate the sort by column name, i thought of using the below to assign the column number, but i am not sure where to call in colNum again in the recorded portion of the macro.

Dim colNum As Integer
colNum = WorksheetFunction.Match("Asset Name", ActiveSheet.Range("1:1"), 0)

Solution

btw defining a range of "50000" rows is not classed as dynamic. Try below

Sub test()
'Setup column names
Col1name = "Asset Name"
Col2name = "Action"

'Find cols
For Each cell In Range("A1:" & Range("A1").End(xlToRight).Address)
    If cell.Value = Col1name Then
        Col1 = cell.Column
    End If
    If cell.Value = Col2name Then
        Col2 = cell.Column
    End If
Next

'Below two line:- if they are blank e.g. column not found it will error so a small bit of error handling 
If Col1 = "" Then Exit Sub
If Col2 = "" Then Exit Sub

'Find last row - dynamic part
lastrow = ActiveSheet.Range("A100000").End(xlUp).Row

'Convert col numer to name
Col1 = Split(Cells(1, Col1).Address(True, False), "$")
Col2 = Split(Cells(1, Col2).Address(True, False), "$")

'Sort
With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range(Col1(0) & "2:" & Col1(0) & lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range(Col2(0) & "2:" & Col2(0) & lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange Range("A1:H" & lastrow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Comments

[Akhil]

Thank you very much! That works! For my understanding (Assuming Asset Name is in column B) - Your code would initially assign Col1 as B1, and afterwards, you split the 1 from it?

[99moorem]

From this Col1 = cell.Column we would get 2 for column B. Split(Cells(1, Col1).Address(True, False), "$") would return us "B$1" then we split on the "$" and get "B" and "1". Later on Col1(0) would reference the "B" part. Col1(1) would ref the "1" part. Hope that helps

[Akhil]

Thanks a lot 99moorem!

[Tom Ruiz]

two years late but wanted to put in my 2 cents... I rather use the lRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row method as its not column dependant and I don't know feels better that way. not that theres nothing wrong with your method...