Custom sort to get empty first

[YasserKhalil]

Hello everyone
I am using the following code to sort in custom way

Code: Select all

Sub Test()
    Dim n As Long
    Application.AddCustomList Array(Empty, "xx", "yy", "rr", "bb", "nn", "tt", "ff")
    n = Application.CustomListCount
    With Sheets("Sheet1")
        .Range("A1").CurrentRegion.Sort Key1:=.Range("AU1"), Header:=xlYes, OrderCustom:=n + 1
        .Sort.SortFields.Clear
    End With
    Application.DeleteCustomList n
End Sub

The array works fine except for the empty cells. How can I get the empty cells first? I tried to put "" and put Empty inside the array but doesn't work

[YasserKhalil]

I could do it by workaround putting a space instead of the empty like that

Code: Select all

Sub Custom_Sort()
    Dim n As Long
    Application.AddCustomList Array(Empty, "xx", "yy", "rr", "bb", "nn", "tt", "ff")
    n = Application.CustomListCount
    With Sheets("Sheet1")
    On Error Resume Next
        .Range("A1").CurrentRegion.Columns(47).SpecialCells(xlBlanks).Value = " "
        On Error GoTo 0
        .Range("A1").CurrentRegion.Sort Key1:=.Range("AU1"), Header:=xlYes, OrderCustom:=n + 1
        .Sort.SortFields.Clear
        .Range("A1").CurrentRegion.Replace What:=" ", Replacement:=Empty, LookAt:=xlWhole
    End With
    Application.DeleteCustomList n
End Sub

But I welcome any other ideas.

[YasserKhalil]

Can I add another column to sort on .. The other column will be ordinary
I tried this line

Code: Select all

.Range("A1").CurrentRegion.Sort Key1:=.Range("N1"), Order1:=xlDescending, Header:=xlYes

But this removes the custom order I did previously.

[HansV]

Does it work if you switch the two sorting actions?

[YasserKhalil]

I think it is working but the custom sort is affeced now and it is in descending order as for the array

[YasserKhalil]

I need to sort according the custom sort.
In other words, to order according the custom sort then sort by column N (descending) then sort by column F (ascending)

[YasserKhalil]

This worked for me

Code: Select all

    .Range("A1").CurrentRegion.Sort Key1:=.Range("N1"), Order1:=xlDescending, Key1:=.Range("F1"), Order1:=xlAscending, Header:=xlYes
        .Range("A1").CurrentRegion.Sort Key1:=.Range("AU1"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=n + 1