Custom List Deletion appears to be corrupting file

laf77

New Member
Joined
May 23, 2005
Messages
16
We are in the process of switching from running Excel 2007 under Windows XP to running Excel 2010 under Windows 7. Most of my Excel reports are not having any issues. However, I have several macros that cause Excel to crash when they go to save the file. Through trial & error, I have found that the problem seems to occur after deleting custom lists that were created earlier in the macro. Here's the pertinent code:

' Declare Variables
Dim VarListNum1
Dim VarListNum2
Dim VarListNum3

' Create Custom Lists
Sheets("Custom Lists").Select
Range("A2:A9").Select 'Order for Yesterday Info
Application.AddCustomList ListArray:=Range("A2:A9")
VarListNum1 = Application.CustomListCount
VarListNum1 = VarListNum1 + 1
Range("A14:A20").Select 'Order for Yesterday-1 Info
Application.AddCustomList ListArray:=Range("A14:A20")
VarListNum2 = Application.CustomListCount
VarListNum2 = VarListNum2 + 1
Range("A26:A33").Select 'Order for Month-to-Date Info
Application.AddCustomList ListArray:=Range("A26:A32")
VarListNum3 = Application.CustomListCount
VarListNum3 = VarListNum3 + 1

' Order Raw Data
On Error Resume Next
Sheets("Sheet1").Select
Range("A1:H200").Select
Selection.Sort Key1:=Range("A1:H200"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum1, MatchCase:=False, Orientation:=xlLeftToRight
Range("J1").Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum1, MatchCase:=False, Orientation:=xlLeftToRight
Sheets("Sheet2").Select
Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum2, MatchCase:=False, Orientation:=xlLeftToRight
Range("J1").Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum2, MatchCase:=False, Orientation:=xlLeftToRight
Sheets("Sheet7").Select
Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum3, MatchCase:=False, Orientation:=xlLeftToRight
Range("J1").Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum3, MatchCase:=False, Orientation:=xlLeftToRight

'Delete Custom Lists
Application.DeleteCustomList (VarListNum3 - 1)
Application.DeleteCustomList (VarListNum2 - 1)
Application.DeleteCustomList (VarListNum1 - 1)


When I execute the macro line-by-line, it is creating & deleting the custom lists as expected. However, if I try to save the file after any of the custom lists have been created, Excel crashes. Has something changed in Excel 2010? Any suggestions for fixing or further troubleshooting?

Thank you,
 
I should have said that after lists created AND deleted, Excel crashes when trying to save the file. I have also found that if no data is actually sorted (none available for time frame), then the file saves normally.
 
Upvote 0
Another user helped me find the answer I needed. After each sort is done, I have to add the following:
ActiveSheet.Sort.SortFields.Clear

Then it works!:)
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top