CODEWITHSUNDEEP
vbasortingexcel
Akhil
Akhil

Reputation: 49

Macro - Multi Level sort by column name

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)

Upvotes: 2

Views: 16719

Answers (2)

user4039065
user4039065

Reputation:

Here is some abbreviated code that should give you everything you are looking for.

With ActiveSheet '<- set this worksheet reference properly
    With .Range("A1:H50000")
        .Cells.Sort Key1:=.Columns(Application.Match("Asset Name", .Rows(1), 0)), Order1:=xlAscending, _
                    Key2:=.Columns(Application.Match("Action", .Rows(1), 0)), Order2:=xlAscending, _
                    Orientation:=xlTopToBottom, Header:=xlYes
    End With
End With

Upvotes: 0

99moorem
99moorem

Reputation: 1983

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

Upvotes: 4

Related Questions