double custom sort on a range problem

[vince g]

I'm still new to VBA but hopefully progressing. That said I'm now stuck on the next stage of a larger project I'm working on.

In short, I need to execute 2 custom sorts on 2 columns of a worksheet range. I've set

I'm using the code below. I've added the 2 custom sorts and can see these within excel as custom lists 5 & 6 ( seen when I don't delete them in VBA - first 4 I know as internal excel ones). The code below does not sort as I specify at all and seems to be alphabetic only. Variable Listn1 has value 5 & listn2 is 6. If I force Listn1 to be 6 the first sort works correctly. Not sure why its not 5. I cannot get the 2nd sort to work at all. Am I missing something obvious here or just plain of the path. I would like to understand my error as part of my education. Alternatively if someone can point be to a better way or some code that would be great. Any help welcome.



Dim Datascope As Range
Dim Listn1 As Long
Dim Listn2 As Long

Set Datascope = Range("A1").CurrentRegion5

Application.AddCustomList Array("Equity", "Bonds", "Property", "Alternate", "Commodity", "Money Market", "Cash")
Listn1 = Application.CustomListCount
Debug.Print "list1"; Listn1
Application.AddCustomList Array("UK", "Global", "Dev", "Emerg", "Europe", "US", _
"Japan", "AsiaPac ex Jap", "Latin America", "Themed", "Frontier", "Country")
Listn2 = Application.CustomListCount
Debug.Print "list2"; Listn2


With Datascope
.Sort Key1:=Range("I1", Range("I1").End(xlDown)), OrderCustom:=Listn1, Header:=xlYes, _
Key2:=Range("J1", Range("J1").End(xlDown)), OrderCustom:=Listn2, Header:=xlYes

Application.DeleteCustomList Listn1
Application.DeleteCustomList Listn2

End With

Immediate Windows shows
list1 5
list2 6

The sheet looks like this post run of the code - copied a section here where 1st coln is actually col I and 2nd is Col J in my main sheet.

CoreAssetClass	Region	Factor 1	Factor 2
Equity	Dev	Large	Value
Equity	Dev	Large	Blend
Equity	Dev	Large	Growth
Equity	Dev	Small	Blend
Equity	Emerg	Large	Blend
Equity	Emerg	Large	Blend
Equity	Emerg	Small-Medium	Blend
Equity	Emerg	Small	Value
Equity	Emerg	Blend	Value
Equity	Emerg	small	Blend
Equity	Emerg	Small	Blend
Equity	Emerg	Blend	Value
Equity	Europe	Medium	Growth
Equity	Europe	Large	Growth
Equity	UK	Small	growth
Equity	UK	Small	growth
Equity	UK	Large	Value
Equity	UK	Large	Blend
Equity	UK	Small	growth
Equity	UK	Medium	Blend
Equity	UK	Large	Value
Equity	UK	Small	Value
Equity	UK	Small	Blend
Bonds	Dev	Strategic	Gov+Corp
Bonds	Dev	Strategic	Gov+Corp
Bonds	Dev	Strategic	Gov+Corp

 

[Fluff]

You cannot use custom sort on two columns with the old style code, you will need to use the new way of sorting.

 

[vince g]

Fluff hi, I'm new to this so was not aware there was an old and new approach. Could you point me at what is considered the new approach so I can try and understand more and solve my issue. Thx

 

[Fluff]

Easiest way is to run your code, without these two lines

Application.DeleteCustomList Listn1
Application.DeleteCustomList Listn2

Then turn on the macro recorder & record yourself manually sorting the data. Turn f the recorder & you have the basis for your code.

 

[Fluff]

The macro recorder code can then be cleaned up, so you would get

Sub Vinceg()
   Dim Lst1 As String, Lst2 As String
   Dim ws As Worksheet
   
   Set ws = ActiveSheet
   Lst1 = "Equity, Bonds, Property, Alternate, Commodity, Money Market, Cash"
   Lst2 = "UK, Global, Dev, Emerg, Europe, US, Japan, AsiaPac ex ***, Latin America, Themed,Frontier, Country"
   With ws.Sort
      .SortFields.Clear
      .SortFields.Add Range("I1"), xlSortOnValues, , Lst1, xlSortNormal
      .SortFields.Add Range("J1"), xlSortOnValues, , Lst2, xlSortNormal
      .SetRange Range("A1").CurrentRegion
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With
End Sub

 

[vince g]

Fluff hi,

many thanks for the code snippet and taking the time to generate it and help me. It works great and I understand it. On a last point I would be interested to understand more on the old and new point you made prior. I assume the 'With' construct is of the new form. Could you point me to somewhere here or elsewhere I could read a little more on this so I understand the history better. If not fine, and thx again for your help.

 

[Fluff]

The With statement has been around a long time & is just a form of "shorthand" so that you don't have to put ws.Sort at the start of each line.
Unfortunately I don't know of any sites that explain the difference between between the two types of sorting, although the "new" way came into being with the release of Excel 2007.