excel crashing when saving after custom sort

[rhombus4]

I have 2 Custom Sorts which both work, but when I run either Code below and then save the workbook excel crashes. Just closes without any message. It will also close other workbooks if I have them open

Sub Custom_Sort1()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1")
Dim rgSort As Range
Set rgSort = ws.Range("A1:Q50")
Dim rgKey As Range
Set rgKey = ws.Range("L1")

Dim sCustomList(1 To 4) As String
sCustomList(1) = "Cat": sCustomList(2) = "Dog": sCustomList(3) = "Bird": sCustomList(4) = "Ape"

 Application.AddCustomList ListArray:=sCustomList

ws.Sort.SortFields.Clear
rgSort.Sort Key1:=rgKey, Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Application.DeleteCustomList Application.CustomListCount
Set ws = Nothing

End Sub

Sub Custom_Sort2()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1")
Dim rgSort As Range
Set rgSort = ws.Range("A1:Q50")
Dim rgKey As Range
Set rgKey = ws.Range("L1")

Dim xyz As Variant
xyz = Array("Cat", "Dog", "Bird", "Ape")

Application.AddCustomList ListArray:=xyz

ws.Sort.SortFields.Clear
rgSort.Sort Key1:=rgKey, Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Application.DeleteCustomList Application.CustomListCount
Set ws = Nothing

End Sub

 

[RoryA]

Why not actually use the Sort object which allows you to specify the sort order directly? For example:

Sub Custom_Sort2()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1")
Dim rgSort As Range
Set rgSort = ws.Range("A1:Q50")
Dim rgKey As Range
Set rgKey = ws.Range("L1")

With ws.Sort
   With .SortFields
      .Clear
      add Key:=rgKey, Order:=xlAscending, Customorder:="Cat,Dog,Bird,Ape", DataOption:=xlSortNormal
   End With
.Setrange rgsort
.Header= xlGuess
.Orientation = xlTopToBottom
end with
Set ws = Nothing

End Sub

 

[rhombus4]

Why not actually use the Sort object which allows you to specify the sort order directly? For example:

Sub Custom_Sort2()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1")
Dim rgSort As Range
Set rgSort = ws.Range("A1:Q50")
Dim rgKey As Range
Set rgKey = ws.Range("L1")

With ws.Sort
   With .SortFields
      .Clear
      add Key:=rgKey, Order:=xlAscending, Customorder:="Cat,Dog,Bird,Ape", DataOption:=xlSortNormal
   End With
.Setrange rgsort
.Header= xlGuess
.Orientation = xlTopToBottom
end with
Set ws = Nothing

End Sub

Had to use .add and then .apply although it sorted the header row as well

 

[Fluff]

it sorted the header row as well

In that case use

.Header= xlYes

 

[rhombus4]

Thanks

I actually changed the range and header no, but I'm assuming both would work

PS Rory: Why not actually use the Sort object which allows you to specify the sort order directly?
Didn't realise you could do it that way, When I searched most people added custom list sorted then deleted the Custom list.

Also I did find an older thread of yours where you mentioned to clear sort field before deleting the custom list. So when I added it at the end of the sort part it stopped crashing. Not sure why it crashes in the first place???

ws.Sort.SortFields.Clear
rgSort.Sort Key1:=rgKey, Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
ws.Sort.SortFields.Clear

Sub Custom_Sort3()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1")

Dim rgSort As Range
Set rgSort = ws.Range("A2:Q50")
Dim rgKey As Range
Set rgKey = ws.Range("L2")

With ws.Sort
   With .SortFields
      .Clear
    .Add Key:=rgKey, Order:=xlAscending, CustomOrder:="Cat,Dog,Bird,Ape", DataOption:=xlSortNormal
   End With
.SetRange rgSort
.Header = xlNo
.Orientation = xlTopToBottom
.Apply
End With
Set ws = Nothing

End Sub

 

[RoryA]

Probably because you've got it sorting using a list that no longer exists. Using the Sort object is better for this sort of thing, in my opinion.