VBA - Sort data by custom list without selecting it or adding auto-filters

**wahbob** · 05-26-2015, 11:53 AM

Hello,

First post after using eveyone's help on here over last couple of years! My VBA skills are improving, but still gaps in my knowledge...

I'm writing a fairly lengthy macro which involves sorting the data numerous times. The first couple of sorts are fairly straight forward and I'm currently doing it using the code below:

Example Code

With Sheets("Sheet1")

With .Range("A7:AO" & LastRowCurrent)

.Sort key1:=.Columns(4), order1:=xlAscending, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

End With

End With

I had previously used a lengthier bit of code which included putting an auto-filter on, but the version above runs through much quicker as I have 30,000+ lines of data to sort.

However, I am unsure how to use a similar styled code to filter by a particular custom order sequence (i.e. not alphabetical or numerical)

I would like the data to be sorted in the following order:

"S1,S2,S3...S30,A1,A2,A3...A30,X1,X2,X3...X30"

Can anyone help as I can't seem to work out how to code this using a similar style code to the example?

Thanks.

**stnkynts** · 05-26-2015, 01:05 PM

Use an array. Here is a good link

http://www.cpearson.com/excel/SortingArrays.aspx

**wahbob** · 05-26-2015, 02:27 PM

Thanks Stnkynts.

I used a bit of the code from there to help get the solution.

For anyone else who is looking for a similar solution, here's how my new code reads:

Example Code

Dim Arr(1 To 5) As String

Arr(1) = "S1"
Arr(2) = "S2"
Arr(3) = "S3"
Arr(4) = "A1"
Arr(5) = "A2"

'extend to cover full list - first 5 given as example.

Application.AddCustomList ListArray:=Arr

With Sheets("Sheet1")

With .Range("A7:AO" & LastRowCurrent)

.Sort key1:=.Columns(4), order1:=xlAscending, _
OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End With

End With