Code an Excel VBA sort with a custom order and a value containing commas
It seems to be missing the Apply
. Can you Add
MyWorksheet.Sort.Apply
The custom order you have is working as is in my sample.
EDIT Updated based on OP updated question
Edit the macro to the following - using an array for the OrderCustom parameter.
Dim oWorksheet As Worksheet
Set oWorksheet = ActiveWorkbook.Worksheets("Sheet1")
Dim oRangeSort As Range
Dim oRangeKey As Range
' one range that includes all colums do sort
Set oRangeSort = oWorksheet.Range("A1:B9")
' start of column with keys to sort
Set oRangeKey = oWorksheet.Range("B1")
' custom sort order
Dim sCustomList(1 To 3) As String
sCustomList(1) = "Cyberspace"
sCustomList(2) = "Aerospace"
sCustomList(3) = "Air, Land, or Sea"
Application.AddCustomList ListArray:=sCustomList
' use this if you want a list on the spreadsheet to sort by
' Application.AddCustomList ListArray:=Range("D1:D3")
oWorksheet.Sort.SortFields.Clear
oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
' clean up
Application.DeleteCustomList Application.CustomListCount
Set oWorksheet = Nothing