We help IT Professionals succeed at work.

VBA Custom Sort Lists Help

Delerium1978 used Ask the Experts™
Hi Guys and Gals,

I wrote the following VBA script in excel to do some custom sorts. The issue I have is that every time i call the sort macro it creates a new custom list. After it got to about 10 lists, it was showing corruptions when i loaded my spreadsheet.

Current script is:

Sub SortSCRList()

'Set Array to get named range into an array for the custom list
Dim vArray()

Dim RowCount As Integer
ReDim vArray(Range("statuslist").Rows.Count - 1)
vArray = WorksheetFunction.Transpose(Range("statuslist"))

RowCount = xlLastRow("SCR List")
LastCol = ColumnNumberToLetter(xllastcol("SCR List"))

NewListCount = Application.CustomListCount + 1

'Here is the custom sort order
Application.AddCustomList ListArray:=vArray

'New Action the Sort (Note: Custom Sort on SCR Status field)
Range("A3:" & LastCol & RowCount).Sort Key1:=Range("D3"), Order1:=xlAscending, Key2:=Range("A3"), OrderCustom:=NewListCount, _
Header:=xlGuess, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, Order2:=xlDescending

End Sub

Open in new window

So what this is doing is:

1) setting an array
2) counting the number of rows in a named range and setting the array equal to the named range
3) Then its setting a new custom list based on that array
4) Then doing the sort

Works great but keeps filling up custom lists. Ideally i want it to delete all custom lists before sorting or at least be a static list location that i can simply update.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
You can use Application.GetCustomlistnum(vArray) to check if the list already exists (you'll get an error if not) and then only add it if required.


Went with looping to see if number of custom lists >4 and if so, deleting lists until its = 4. Then just adding new array as new list :)
Most Valuable Expert 2011
Top Expert 2011

Why accept my answer if you just ignored it? ;)