Excel VBA Sort using a non standard method (custom)

[dtrynoski]

Need help. I have a spreadsheet where I need to sort my data by column F using a specific text order (red, yellow, green, hold, complete). Whats the best method to accomplish this? Can it be done using Range? or do I have to create an array?

I've specified my dataset range as follows:

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:H" & FinalRow).Select

 

[dtrynoski]

I believe I found the answer, but maybe there is a better way. This is what I'm using:

With Application
.AddCustomList ListArray:=Array("Red", "Yellow", "Green", "Hold", "Complete")
x = .CustomListCount
Range("A2:H" & FinalRow).Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=x, MatchCase:=False, Orientation:=xlTopToBottom
.DeleteCustomList ListNum:=x
End With

 

[Eric W]

Welcome to the forum.

Try:

    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A2"), CustomOrder:="red,yellow,green,hold,complete"
        .SetRange Range("A:A")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With

Looks like you found a solution, but you can include the custom list in the sort itself if you want.