Sort by three columns one of them is custom sort

[YasserKhalil]

Hello everyone
I manage to sort data by two columns one of them is custom like that

Code: Select all

                Dim n As Long
    Application.AddCustomList Array("oo", "ww", "yy", "ss", "nn", "hh")
    n = Application.CustomListCount
    With ActiveSheet
        .Range("A1").CurrentRegion.Sort Key1:=.Range("J1"), Key2:=.Range("D1"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=n + 1
        .Sort.SortFields.Clear
    End With
    Application.DeleteCustomList n

I need to add a third column which is K to be the second key
so the sort would be first by column J (this is the custom column related to the custom array) then by column K (descending) then by column D (ascending)

[YasserKhalil]

I tried such approach but throws an error

Code: Select all

With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("J1"), OrderCustom:=n + 1
        .SortFields.Add Key:=Range("K1"), Order:=xlDescending
        .SortFields.Add Key:=Range("D1"), Order:=xlAscending
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .Apply
    End With
Application.DeleteCustomList n

[YasserKhalil]

I tried the following and worked. I didn't expect that this approach takes more than two keys (new for me)

n = Application.CustomListCount
With ActiveSheet
.Range("A1").CurrentRegion.Sort Key1:=.Range("J1"), Key2:=.Range("K1"), Order2:=xlDescending, Key3:=.Range("D1"), Order3:=xlAscending, Header:=xlYes, OrderCustom:=n + 1
.Sort.SortFields.Clear
End With
Application.DeleteCustomList n

[HansV]

Code: Select all

        .SortFields.Add Key:=Range("J1"), OrderCustom:=n + 1

Instead of OrderCustom, use CustomOrder

[YasserKhalil]

Thank you very much, my tutor.

[YasserKhalil]

I tried CustomOrder as you guide me, but this throws error too.

[HansV]

Try this:

Code: Select all

    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("J1"), CustomOrder:="oo,ww,yy,ss,nn,hh"
        .SortFields.Add Key:=Range("K1"), Order:=xlDescending
        .SortFields.Add Key:=Range("D1"), Order:=xlAscending
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .Apply
    End With

[YasserKhalil]

Amazing. That worked like charm. Thank you very much.