Code an Excel VBA sort with a custom order and a value containing commas

by

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

More Related Contents:

  1. VBA Excel sort range by specific column
  2. Excel VBA Quickest way to sort an array of numbers in descending order?
  3. Format Cell Contents
  4. Excel formula checklist in employees
  5. How do I put double quotes in a string in vba?
  6. How can I URL encode a string in Excel VBA?
  7. How do I slice an array in Excel VBA?
  8. Excel VBA deleting rows in a for loop misses rows
  9. Download attachment from Outlook and Open in Excel
  10. What is the most efficient/quickest way to loop through rows in VBA (excel)?
  11. Embed picture in outlook mail body excel vba
  12. 2 ways for “ClearContents” on VBA Excel, but 1 work fine. Why? [duplicate]
  13. VBa conditional delete loop not working
  14. Return Index of an Element in an Array Excel VBA
  15. Excel VBA: Range to String Array in 1 step
  16. Run-time error ‘1004’ : Method ‘Range’ of object’_Global’ failed
  17. “Can’t find Project or Library” for standard VBA functions
  18. How do I issue an HTTP GET from Excel VBA for Mac
  19. Export chart as image – with click of a button
  20. Error13 in Excel VBA in data file
  21. How to create dynamic variable names VBA
  22. Change font color for a part of text in cell
  23. Excel cell from which a Function is called [duplicate]
  24. Subscript out of range error with an array – no idea why?
  25. How to extract text within a string of text
  26. GET pictures from a url and then rename the picture
  27. Excel VBA like operator [closed]
  28. Access files with long paths (over 260)
  29. Concatenate multiple ranges using vba
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment