Use VBA to sort data based on priority

Question

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    On Error Resume Next
    Range("E1").Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False,  Orientation:=xlTopToBottom
End Sub

I'm currently using this code to sort reports based on the date of processing in column E. It works great, but I also have to do another sorting based on priority (High, Medium, Low) so that each day is also sorted with the highest priority at the top.

E.g.

Task one / High / 12-02-18

Task two / High / 13-02-18

Task three / Medium / 13-02-18

Task four / Low / 13-02-18

Task five / High / 14-02-18

I'm pretty new to VBA and would greatly appreciate help for this.

score 1 · Accepted Answer · answered Feb 13 '18 at 17:05

The following code will sort Column E in ascending order and then it will take column B with the Priorities and sort with a custom list of High,Medium,Low:

Sub foo()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'declare and set your worksheet, amend as required
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("E2:E" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ws.Sort.SortFields.Add Key:=Range("B2:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="High,Medium,Low", DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("A1:E" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thank you! It worked perfectly and that code is a lot easier to understand. — James D., Feb 13 '18 at 19:07

DisplayName · Answer 2 · 2018-02-13T19:14:28.610

Sort() method of Range object allows for up to three sorting keys

so, assuming that your data spans from column A to E and you want to sort on column D and E, you may code:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    With Worksheets("MySheetToSortName") ' change "MySheetToSortName" to your actual sheet to sort name
        .Columns("A:E").Sort Key1:=.Range("E1"), Order1:=xlAscending, Key2:=.Range("D1"), Order2:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
End Sub

to sort on custom order you want to use Sort object like per this post

while as a funny (?) workaround sticking to Sort() method, you can act as follows:

Private Sub main()
    With Worksheets("Sheet1")
        With .Columns("D")
            .Replace "High", "1"
            .Replace "Medium", "2"
            .Replace "Low", "3"
        End With
        .Columns("A:E").Sort Key1:=.Range("E1"), Order1:=xlAscending, Key2:=.Range("D1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        With .Columns("D")
            .Replace "1", "High"
            .Replace "2", "Medium"
            .Replace "3", "Low"
        End With
    End With
End Sub

Use VBA to sort data based on priority

2 Answers2