Sort Macro

N

nc

Can I put the sort list (Similar to a custom list) in a
macro?

I can use the addcustmlist method to add the list to the
custom lists, the problem is when recording the macro to
sort according to that customlist the code selects the
list according to it's position in the in the custom
lists. This lists might be different in a different order
if run on another users computer.
 
D

Dave Peterson

There's an application.getcustomlist that you can use to retrieve that custom
list number.

Option Explicit
Sub testme()

Dim myList As Variant
Dim myCustomListNumber As Long

myList = Array("aaaa", "bbbb", "abab", "aaba", "aaab")

With Application
.AddCustomList listarray:=myList
myCustomListNumber = .GetCustomListNum(listarray:=myList)
End With

With Worksheets("sheet1")
.Range("a1:e" & .Cells(.Rows.Count, "A").End(xlUp).Row).Sort _
Key1:=.Range("a1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=myCustomListNumber + 1
End With

With Application
.DeleteCustomList myCustomListNumber
End With

End Sub
 
N

nc

Thanks Dave.

Why is it when deleting myCustomListNumber is used but for
sorting the OrderCustom used is myCustomListNumber + 1? I
was expecting it to be the former for both.
 
D

Dave Peterson

Why?

'cause that's the way excel works <vbg>. I don't have any idea, but it is
documented in VBA's help for the Sort command:

OrderCustom Optional Variant. This argument is a one-based integer offset to
the list of custom sort orders. If you omit OrderCustom, a normal sort is used.

And the help for deletecustomlist doesn't do that offset.

ListNum Required Long. The custom list number. This number must be greater
than or equal to 5 (Microsoft Excel has four built-in custom lists that cannot
be deleted).

====
It drove me nuts, too. In fact, each time I do it, I remember that there's
something weird and off I go to the help!
 
Top