Custom Sort

[Portucale]

Hi,

Not really sure what is wrong with the code below but every time I run it excel application crash, I would appreciate if you figure out where am I going wrong as I can't see any issues...

Sub customsort()
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
End Sub

After some more investigation, it seems that is the part where is deleting the custom list.

 Application.DeleteCustomList Application.CustomListCount

any ideas why?

Thanks


I pick this code from "http://stackoverflow.com/questions/...-a-custom-order-and-a-value-containing-commas".

Any help and ALL help is very much appreciated.

Kind regards,

 

[Portucale]

Hi,

Possibly I have answer my own question...

I have added a line of code

 ActiveSheet.Sort.SortFields.Clear

before deleting the Custom list, and works perfectly.

source: http://answers.microsoft.com/en-us/...4f8d-ae96-de8eb75598a6?rtAction=1415710216848
look to the "Most Helpful Reply" by kalkulon

However if is there any other ideas I would be grateful.

Thanks in advance,