We help IT Professionals succeed at work.

VBA Custom Sort Lists Help

Delerium1978
Delerium1978 used Ask the Experts™
on
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.

James
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
Commented:
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.

Author

Commented:
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

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