How to Sort By Using VBA to Reference Cells Containing Values for (1) Ranges & (2) Multiple Keys)

[MikeUsername]

Hello all,

The Problem
I am trying to use VBA to sort a named range ("MyRange1") -- which is defined as ("A10:E20"), with column headings in ("A9:E9").
I would like to sort by multiple (three) columns/keys).

The Referenced Columns/Keys
(1) The primary sort column/key's value -- MartialArtsCategoryRange, (which references "A10:A20) -- is the value in named range ("Criteria1") -- in cell A2.
(2) The secondary sort column/key's value -- MartialArtsSubcategoryRange (which references "B10:B20") -- is the value in named range ("Criteria2") -- in cell B2.
(3) The third sort column/key's value -- MartialArtsNameRange (which references "C10:C20") -- is the value in named range ("Criteria3"") -- in cell C2.

The Current Result
The below code I have tried does not result in data sorted by three columns/keys simultaneously but rather results in data sorted by the third column/key (named range "Criteria3", with contains the value MartialArtsName) only. Any suggestions would be much appreciated.

Code Used
Sub SortingByMultipleCriteria()


Range("MyRange1").Sort Key1:=Range("Criteria1"), Order1:=xlAscending, Key2:=Range("Criteria2"), Order2:=xlAscending, Key3:=Range("Criteria3"), Order3:=xlAscending


End Sub

 

[Marcelo Branco]

Welcome to Mr Excel

Your code worked perfectly for me.

But you also can try this

Sub Sorting()

    With Range("MyRange1")
        .Sort Key1:=.Cells(1), Order1:=xlAscending, _
            Key2:=.Cells(2), Order2:=xlAscending, _
            Key3:=.Cells(3), Order3:=xlAscending, Header:=xlNo
    End With

End Sub

M.