Custom Sort Order

splion · Dec 24th 2020

I try to sort based upon a custom sort order, which is an array. I populated the custom sort order array from a range of cell values.

Why can't I just call the name of the array as below?

Code

CustomOrder:=SortOrderArray

Each time that I run the VBA Macro, it adds another Custom List. These accumulate and I think that they cause issues. Can I delete all old custom sort lists at the start of a run?

Why do I need to use Order:=xlAscending? Does this interfere with my use of the Custom List?

Code

Range("A2:C61").Select
Application.DeleteCustomList ListNum:=9
Application.AddCustomList ListArray:=Array("PUH IV", "PUH D PUH Charge", "PUH Flex Shift")
ActiveWorkbook.Worksheets("Output2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Output2").Sort.SortFields.Add2 Key:=Range("B2:B61" _
), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"PUH IV,PUH D PUH Charge,PUH Flex Shift" & _
"", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Output2").Sort
.SetRange Range("A2:B61")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Display More

splion · Dec 24th 2020

Would it be easier to just number the items in the SortOrderArray, find the position of each value in in my data in the SortOrderArray and write the numbers to an adjacent column, then sort the entire table based on those numbers? It wouldn't be that tedious and it would add an extra column, but I think that it would make my sort function much more robust.

Fluff13 · Dec 24th 2020

You can get rid of these lines

Code

Application.DeleteCustomList ListNum:=9
Application.AddCustomList ListArray:=Array("PUH IV", "PUH D PUH Charge", "PUH Flex Shift")

as you already have the custom list in the code.

Custom Sort Order

Share

Similar Threads

VBA: Print page range

Hiding and Un-Hiding Rows - Some rows hide and some will not

VBA to Find Occurence of Word, Then Custom Sort LOOP

Tags