VBA Custom Sort on UsedRange

[Barklie]

Hello,

I am trying to do a custom sort on a UsedRange. The table is dynamic, but I will always want to sort on Column E.

I tried the code below, but it is throwing an error.

Sub Macro3()
ActiveSheet.UsedRange.Sort Key1:=Range("E1"), Order1:=xlAscending, _
    CustomOrder:="Hot,Warm,Neutral,Cool,DNC,Ineligible", Header:=xlYes
    
End Sub

Thanks,
Barklie

 

[JoeMo]

Hello,

I am trying to do a custom sort on a UsedRange. The table is dynamic, but I will always want to sort on Column E.

I tried the code below, but it is throwing an error.

Sub Macro3()
ActiveSheet.UsedRange.Sort Key1:=Range("E1"), Order1:=xlAscending, _
    CustomOrder:="Hot,Warm,Neutral,Cool,DNC,Ineligible", Header:=xlYes
    
End Sub

Thanks,
Barklie

You are confusing the SortFields.Add Method which has a CustomOrder argument with the Range.Sort Method which has an OrderCustom argument (see: https://msdn.microsoft.com/en-us/library/office/ff840646.aspx). To use the latter Method you need to place your custom list into Excel's list of custom sort orders. This should do that:

Sub Macro3()
Dim vSortList As Variant
vSortList = Array("Hot", "Warm", "Neutral", "Cool", "DNC", "Ineligible")
'add the custom list to your existing custom lists
Application.AddCustomList ListArray:=vSortList
ActiveSheet.UsedRange.Sort key1:=[E2], Header:=xlYes, ordercustom:=Application.CustomListCount + 1
End Sub

 

[Barklie]

Aha, thank you so much. I had just finished doing it the other way (see below), but I would guess your code is a little more robust so I'm gonna go with that.

Sub Macro3()
ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Add Key:=Range("E2:E" & Cells(Rows.Count, 2).End(xlUp).Row), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Hot,Warm,Neutral,Cool,DNC,Ineligible", DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet4").Sort
        .SetRange Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
        .Header = xlYes
        .Apply
    End With
End Sub

 

[JoeMo]

Aha, thank you so much. I had just finished doing it the other way (see below), but I would guess your code is a little more robust so I'm gonna go with that.

You are welcome. Just FYI using the Range.Sort Method that I gave you, places your custom list in Excel's custom lists and in your registry so it is available to any workbook you open. Should you wish to remove it (in Excel 2010, but similar for other versions): File>Options>Advanced>General>Edit Custom Lists. Select your list and click Delete.