Custom order sort

Question

Hi I would like to sort the whole C column based on the values(Critical, high, medium,low). I am running this code on macro enabled worksheet

Here is my code.

Sub run()
Range("C:C").Sort Key1:=Range("C1"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Critical,High,Medium,Low", DataOption:=xlSortNormal
End Sub

It did not work as there is error indicated. No argument. What is the solution to correct this problem? Thank you.

score 2 · Accepted Answer · answered Oct 17 '18 at 07:08

Your custom sort criteria needs to be in an array. Try,

Sub runSortC()
    Dim vCustom_Sort As Variant, rr As Long

    vCustom_Sort = Array("Critical","High","Medium","Low", Chr(42))
    Application.AddCustomList ListArray:=vCustom_Sort

    with Range("C:C")
        .parent.Sort.SortFields.Clear

        'sort on custom order with header
        .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                    Orientation:=xlTopToBottom, Header:=xlYes, MatchCase:=False, _
                    OrderCustom:=Application.CustomListCount + 1

        .parent.Sort.SortFields.Clear

    End With

End Sub

If this is in a public module, a qualified parent worksheet reference would help.

score 0 · Answer 2 · answered Oct 17 '18 at 06:54

Try:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A:A") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
    "Critical,High,Medium,Low", DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A:A")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Custom order sort

2 Answers2

Linked