SORT WITH CUSTOM ORDER

[oblix]

Hi there
I have the following code to sort according to a custom list:

Sub Sort_perm()

    ActiveWorkbook.Worksheets("Perm Transfer").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Perm Transfer").Sort.SortFields.Add Key:=Range( _
        "Q3:Q1000"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "Brig Gen,Col,Lt Col,Maj,Capt,CO,WO1,WO2,F Sgt,Sgt,Cpl,L Cpl,Amn,Mr,Me" _
        , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Perm Transfer").Sort
        .SetRange Range("O2:Z1000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("Q8").Select
End Sub

Can someone please assist to clean up this code
Thank you in advance

 

[Alex Blakenburg]

Your code works so not exactly sure what you had in mind, here is a minor variation.

Sub Sort_perm()
    Dim lastRow As Long

    With ActiveWorkbook.Worksheets("Perm Transfer")
        lastRow = .Range("O" & Rows.Count).End(xlUp).Row            ' Pick appropriate column if not "O"
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range( _
            "Q3:Q" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
            "Brig Gen,Col,Lt Col,Maj,Capt,CO,WO1,WO2,F Sgt,Sgt,Cpl,L Cpl,Amn,Mr,Me" _
            , DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("O2:Z" & lastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

End Sub

 

[oblix]

Thank you
Looks much more professional than mine
If someone types in other than on sort list will it be put at the bottom of list by default?

 

[Alex Blakenburg]

If someone types in other than on sort list will it be put at the bottom of list by default?

It sure seems that way. (To the top if you do descending though)

 

[oblix]

Thanks