CODEWITHSUNDEEP
excelvbasorting
bhbennett3
bhbennett3

Reputation: 123

Why is my data-sorting macro not performing the sorts in the correct order?

I have some very simple VBA code

    With ActiveSheet.Sort
        .SortFields.Add Key:=Range("Stage"), Order:=xlAscending
        .SortFields.Add Key:=Range("Category"), Order:=xlAscending
        .Header = xlYes
        .Apply
    End With

My understanding is that, for example, this mock table

    Stage    Category
   1-First   2-Finance
   4-Fourth  2-Finance
   2-Second  3-Operations
   1-First   1-Strategy

should be sorted as

    Stage    Category
   1-First   1-Strategy
   1-First   2-Finance
   2-Second  3-Operations
   4-Fourth  2-Finance

Yet what I am getting when I run the Macro is

    Stage    Category
   1-First   1-Strategy
   1-First   2-Finance
   4-Fourth  2-Finance
   2-Second  3-Operations

Why is Category taking preference?

Upvotes: 0

Views: 920

Answers (1)

teylyn
teylyn

Reputation: 35915

This one works for me, using your data. Add the .SetRange parameter.

Sub test()
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    With ActiveSheet.Sort
        .SetRange Range("A1:B5")
        .SortFields.Add Key:=Range("Stage"), Order:=xlAscending
        .SortFields.Add Key:=Range("Category"), Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
End Sub

Upvotes: 1

Related Questions