Multi-column sort custom order

[Echilon]

I'm using VBA to sort columns in Excel 2003. I need to sort by column 5 ascending, then column 3 using a custom order, then by column 4 ascending. I'm having difficulty getting the sort to work and I don't totally understand how OrderCustom applies.

Could anyone point point me in the right direction? My code is below.

With wsData
    lastrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    lastCol = .Cells(4, Columns.Count).End(xlToLeft).Column
   
    Dim n As Long
    Application.AddCustomList ListArray:=Array("LOW", "MEDIUM OR HIGH", "HIGH ONLY")
    n = Application.GetCustomListNum(Array("LOW", "MEDIUM OR HIGH", "HIGH ONLY")) + 1

    Dim strSortOrder As String
    .Range(.Cells(1, 1), .Cells(lastrow, lastCol)).Sort _
        Key1:=.Range(.Cells(2, 5), .Cells(lastrow, lastCol)), Order1:=xlAscending, _
        Key2:=.Range(.Cells(2, 3), .Cells(lastrow, lastCol)), Order2:=xlDescending, _
        Key3:=.Range(.Cells(2, 4), .Cells(lastrow, lastCol)), Order3:=xlDescending, _
        OrderCustom:=n, _
        MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes
End With

 

[pgc01]

Hi Echilon
Welcome to the board

Don't mix different values of the custom order in the same sort.
In this case sort 3 times, in the reverse order of what you want it to be the final result.

Try:

    .Range(.Cells(1, 1), .Cells(lastrow, lastcol)).Sort _
        Key1:=.Range(.Cells(2, 4), .Cells(lastrow, lastcol)), Order1:=xlAscending, _
        OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes
    
    .Range(.Cells(1, 1), .Cells(lastrow, lastcol)).Sort _
        Key1:=.Range(.Cells(2, 3), .Cells(lastrow, lastcol)), Order1:=xlDescending, _
        OrderCustom:=n, _
        MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes
    
    .Range(.Cells(1, 1), .Cells(lastrow, lastcol)).Sort _
        Key1:=.Range(.Cells(2, 5), .Cells(lastrow, lastcol)), Order1:=xlAscending, _
        OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes

 

[Echilon]

Cheers, works perfectly!