Excel VBA - multi level sorting

Question

How do I change the code below to sort in a multi level way? At present, the code sorts the table one column at a time, I want to sort it together as a multi level sort.

Below is what Im trying to achieve:

Here's my code which sorts the table one column at a time:

Range("A4:L" & lastRow).Sort key1:=Range("A4:A" & lastRow), _
    order1:=xlAscending, Header:=xlNo
Range("A4:L" & lastRow).Sort key1:=Range("B4:B" & lastRow), _
    order1:=xlAscending, Header:=xlNo
Range("A4:L" & lastRow).Sort key1:=Range("C4:C" & lastRow), _
    order1:=xlAscending, Header:=xlNo
Range("A4:L" & lastRow).Sort key1:=Range("D4:D" & lastRow), _
    order1:=xlAscending, Header:=xlNo
Range("A4:L" & lastRow).Sort key1:=Range("E4:E" & lastRow), _
    order1:=xlAscending, Header:=xlNo

How do I change the above to sort everything together?

user4039065 · Accepted Answer · 2017-03-28 15:55:32Z

4

I always recommend getting rid of the recorded .Sort method in favor of 'only what you need' VBA Sort code. However, there is a problem in that you can only sort a maximum of three sort keys per sort; the solution is to perform two sort operations. Sort the highest ordinals first then the last three primary sort ordinals.

With Worksheets("Sheet1").Range("A4:L" & lastRow)
    .Cells.Sort Key1:=.Columns("D"), Order1:=xlAscending, _
                Key2:=.Columns("E"), Order2:=xlAscending, _
                Orientation:=xlTopToBottom, Header:=xlYes
    .Cells.Sort Key1:=.Columns("A"), Order1:=xlAscending, _
                Key2:=.Columns("B"), Order2:=xlAscending, _
                Key3:=.Columns("C"), Order3:=xlAscending, _
                Orientation:=xlTopToBottom, Header:=xlYes
End With

You've mashed together cell addresses with table columns or header labels in hte image so I am not sure if I got the ordinals right. The above will sort with column A as the primary, B as secondary, C as third, D as fourth and E as fifth.

answered Mar 28, 2017 at 15:55

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

Boosted_d16 Over a year ago

I get a "Subscript out of range" error, sorry I'm new to VBA.

user4039065 Over a year ago

Glad to hear. Yes, I just retested it and it seems to be working. Thanks for the followup.

N3R4ZZuRR0 · Accepted Answer · 2019-09-30 06:09:44Z

0

s = ComboBox1.Text
sr = ComboBox1.Text & "4"

Dim xlSort As XlSortOrder
Dim LastRow As Long

With ActiveSheet
  LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
  Sheet9.Range("b4:k5002").Sort Key1:=Sheet9.Range(sr), Order1:=xlAscending, Key2:=Sheet9.Range("e4"), Order2:=xlAscending
End With

edited Sep 30, 2019 at 6:09

N3R4ZZuRR0

2,4224 gold badges21 silver badges32 bronze badges

answered Sep 29, 2019 at 18:20

00989021570161

11 bronze badge

1 Comment

Trenton McKinney Over a year ago

Here are some guidelines for How do I write a good answer?. This provided answer may be correct, but it could benefit from an explanation. Code only answers are not considered "good" answers. From review.

Collectives™ on Stack Overflow

Excel VBA - multi level sorting

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related