Macro Custom Sort

[howard]

I have data in Cols A to C (headers are in row 1). I have tried to write code to sort the data in Col C based on a custom order on sheet "Unique" . These are listed in Z1 to Z5 on this sheet.

I get a run time error "Application-defined or object-defined error" and the code below is highlighted

It would be appreciated if someone could amend my code

  .Range("A1:C" & Lr).Sort Key1:=.Range("C1"), Order1:=xlAscending, _
            customOrder:=customOrder, MatchCase:=False, Header:=xlYes

See full Code below

 Sub Sort_Bank_Accounts()
    Dim Lr As Long
    Dim customOrderRange As Range
    Dim customOrder() As Variant
    
    ' Set the custom order range
    Set customOrderRange = Sheets("Unique").Range("Z1:Z5")
    
    ' Read the custom order values into an array
    customOrder = customOrderRange.Value
    
    With Sheets("Bank Accounts")
        Lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A1:C" & Lr).Sort Key1:=.Range("C1"), Order1:=xlAscending, _
            customOrder:=customOrder, MatchCase:=False, Header:=xlYes
    End With
End Sub

 

[howard]

See Link Below for Sample Data

Sort by Custom.xlsm

Shared with Dropbox

www.dropbox.com

 

[howard]

I have also posted on Macro to Sort in Specific Order

 

[Kaper]

And it's answered there. But to keep it available directly here I'm posting my solution (on excelforum there is also a second one) here:

Sub Sort_ByType()
Dim Lr As Long

' Set the custom order list
Application.AddCustomList ListArray:=Sheets("Unique").Range("Z1:Z5")

With Sheets("Account Numbers")
  Lr = .Cells(.Rows.Count, "C").End(xlUp).Row
  .Range("A1:C" & Lr).Sort Key1:=.Range("C2"), Order1:=xlAscending, _
    OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, Header:=xlYes
End With

' clean up
Application.DeleteCustomList Application.CustomListCount
End Sub

 

[howard]

Thanks for the code Kaper