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?
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