12

In VBA, Excel allows sorting values using the CustomOrder parameter to choose the sequence items are ordered. Unfortunately, the sequence of items is delimited by commas and one of my sort items contains commas. For example, I want to sort the data in the first column by the categories in the second column. The "Air, Land, or Sea" category contains commas.

Data1     Aerospace
Data2     Cyberspace
Data3     Cyberspace
Data4     Air, Land, or Sea
Data5     Aerospace
Data6     Air, Land, or Sea
Data7     Cyberspace

If you record a VBA macro, the code created looks like this:

MyWorksheet.Sort.SortFields.Add Key:=Range( _
    "B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, _
    CustomOrder:= "Cyberspace,Air,Land,or Sea,Aerospace", _
    DataOption:=xlSortNormal  
MyWorksheet.Sort.Apply

So, the custom sort order should be "Cyberspace" then "Air, Land, or Sea", then "Aerospace". However, the second category is treated as three categories because of the commas. The rows with "Air, Land, or Sea" get sorted to the bottom because Excel doesn't find a custom sort match for them. Is there a way to get CustomOrder to work with a category that contains embedded commas?

I tried putting double quotes around the category and I tried replacing the delimiter commas with semicolons (in the hope Excel would accept a semicolon instead of a comma). Neither worked.

Dean Hill
  • 4,369
  • 6
  • 31
  • 35

3 Answers3

19

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
Steve Mallory
  • 4,245
  • 1
  • 28
  • 31
  • My original example wasn't very clear. Please read the updated question. – Dean Hill May 24 '11 at 16:21
  • 1
    @Dean Hill Updated my answer. – Steve Mallory May 24 '11 at 17:38
  • 4
    I followed this solution which works - except that it causes Excel 2013 to crash when trying to save after running the macro. The solution is to add the line: `ActiveSheet.Sort.Sortfields.Clear` Before the line: `Application.DeleteCustomList Application.CustomListCount` Details here: [answers.microsoft.com link](http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom/using-deletecustomlist-in-a-vba-macro-causes-excel/dbf3ec99-0490-4f8d-ae96-de8eb75598a6?auth=1) – Slab Nov 07 '16 at 11:15
  • @Slab , thanks for this, my excel is still crashing after applying your solution, any other work arounds? – excelguy Jul 03 '18 at 14:52
  • @excelguy Sorry, I'm not sure. Perhap the last comment on my microsoft link might help? "you have to clear the sortfields on every worksheet where it was used" – Slab Jul 09 '18 at 13:10
0

OK...based on the updated description, how about a formula for the column next to what you're sorting.

So, if "Air, Land, or Sea" is in column B1, then C1 would have this:

=SUBSTITUTE(B1,",","|")

Then you could do your custom sort like so:

MyWorksheet.Sort.SortFields.Add Key:=Range( _

        "B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        CustomOrder:= "Cyberspace,Air|Land|or Sea,Aerospace", _
        DataOption:=xlSortNormal  
    MyWorksheet.Sort.Apply

Make sure to adjust the range appropriately.

ray
  • 8,521
  • 7
  • 44
  • 58
0

Using additional quotation marks should also work OK

CustomOrder:="""Cyberspace,Air"",""Land"",""or Sea,Aerospace"""
Robert Cameron
  • 1