Automatic Multi-level Sorting in VBA

Question

I currently have the following code that works and every time something is typed in, it is automatically sorted by column I

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Column = 9 Then
        Dim lastRow As Long
        lastRow = ActiveSheet.Cells.Find(What:="*", LookIn:=xlValues, 
               SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
        Range("A2:I" & lastRow).Sort Key1:=Range("I2:I" & lastRow), 
              Order1:=xlAscending, Header:=xlNo

    End If
End Sub

I want it so that it is primarily sorted by item I, but also secondarily sorted by column A. Any help please?

Wouldn't that be pretty annoying for whoever is entering the data? — Tim Williams
– Tim Williams, Commented Nov 28, 2017 at 20:54
It it sent to automatically sort after column 9 is entered so that is the last piece of data for the line, so doesn't really affect the user. This is like a raw data sheet that automatically generates all the other sheets — Pretzel Stands
– Pretzel Stands, Commented Nov 28, 2017 at 21:04

jeffreyweir · Accepted Answer · 2017-11-28 21:29:31Z

2

The starting point for any request like this should be "Fire up the macro recorder, perform a sort, and see what code it spits out".

If you fire up the recorder and add a multilevel sort like so:

...then the Macro Recorder spits out this:

Sub Macro11()
'
' Macro11 Macro
'

'
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I2:I16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:I16")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This gives you a clue as to the syntax you need to add multiple sort keys. Do you need help amending your original code, or is this enough to get you started?

answered Nov 28, 2017 at 21:29

jeffreyweir

4,8841 gold badge19 silver badges27 bronze badges

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

2 Comments

Pretzel Stands Over a year ago

I will look into this but it will definitely get me started, thank you!

jeffreyweir Over a year ago

Note that some of the stuff the Macro Recorder spits out is redundant. But when working out how to do stuff, the Macro Recorder is your friend.

Collectives™ on Stack Overflow

Automatic Multi-level Sorting in VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related