Use multiple custom lists to sort

Question

I'm trying to use two custom lists to order the columns in a specific reach.

First I define the two custom lists (arrays) and in the .Sort statement I give the column reach, the first column on which to sort including the first custom list, followed by the second column on which to sort with the second custom lists.

VBA only sorts on the first custom list and does nothing with the second custom list.

I haven't found an example using more than one custom list with a self chosen array.

When I look in Excel, the order of the second custom list (key2, E >> A) isn't there. Instead it takes a random/other order.

How can I add a second level of sorting also with a custom sort list?

ordercustom:=Application.CustomListCount + 2 isn't taking the correct custom list.

Sub MultipleCustomLists()

Dim vSortLIst As Variant
Dim vSortLIst2 As Variant

vSortLIst2 = Array("E", "D", "C", "B", "A")
vSortLIst = Array("1", "2", "3", "4", "5")

Application.AddCustomList ListArray:=vSortLIst2
Application.AddCustomList ListArray:=vSortLIst

Columns("A:C").Sort Key1:=[A:A], ordercustom:=Application.CustomListCount + 1, _
  key2:=[B:B], ordercustom:=Application.CustomListCount + 2, _
  Orientation:=xlTopToBottom, Header:=xlYes

ActiveSheet.Sort.SortFields.Clear

Application.DeleteCustomList Application.CustomListCount
Application.DeleteCustomList Application.CustomListCount

End Sub

Answer 1

This worked for me - note the list indexes in the Sort method

Sub MultipleCustomLists()
    
    With Application
        Debug.Print "before add", .CustomListCount
        .AddCustomList ListArray:=Array("E", "D", "C", "B", "A")
        .AddCustomList ListArray:=Array("1", "2", "3", "4", "5")
        Debug.Print "after add", .CustomListCount
    End With
    DoEvents
    
    ActiveSheet.Range("A:C").Sort _
        Key1:=[A2], ordercustom:=Application.CustomListCount, _
        key2:=[B2], ordercustom:=Application.CustomListCount + 1, _
        Orientation:=xlTopToBottom, Header:=xlYes
    
    ActiveSheet.Sort.SortFields.Clear
    
    With Application
        .DeleteCustomList .CustomListCount
        .DeleteCustomList .CustomListCount
        Debug.Print "after delete", .CustomListCount
    End With

End Sub