2

I wish to carry out a custom sort on a selected range at the very end of some standard sorts. So first we sort the range "C", then "R", then "G" and finally I wish to sort on status- which is the custom sort part. But this is going very wrong. I am aware I need a List array aside from that I am unsure how to go about this...please help!

      Dim keyRange(1 To 5) As String
      keyRange(1) = "L-2sd"
      keyRange(2) = "L-1sd"
      keyRange(3) = "P"
      keyRange(4) = "U+1sd"
      keyRange(5) = "U+2sd"


      Application.AddCustomList ListArray:=keyRange
      sortNum = Application.CustomListCount

         'Dim sortNum As Long
       ThisWorkbook.Sheets("Order").Activate
       ThisWorkbook.Sheets("Order").Range("A1:" & Letter & 10000).Select
       Selection.Sort key1:=Range("C"), Order1:=xlAscending,key2:=Range("R"), order2:=xlAscending, key3:=Range("G"), order3:=xlAscending, 

      key4:=Range("status"), Order1:=xlAscending, OrderCustom:=Application.CustomListCount + 1
Improve this question
3
  • 2
    Range.Sort only takes at maximum of 3 keys. You'll need to devise some sort of custom function/procedure to handle the custom aspect of this sort, I think. Or see here for some other suggestions. Commented Dec 23, 2016 at 15:29
  • Gooooood Point!!!! Commented Dec 23, 2016 at 15:35
  • Any ideas of how to do that...? Sort the first 3 then... Commented Dec 23, 2016 at 15:37

2 Answers 2

Reset to default
3

You can sort on many columns from VBA, more than 3, but you'll have to use a different method:

With ThisWorkbook.Worksheets("Order")
    .Sort.SortFields.Clear
    .Sort.SetRange .Range("A1:" & Letter & 10000)
    .Sort.SortFields.Add .Columns("C")
    .Sort.SortFields.Add .Columns("R")
    .Sort.SortFields.Add .Range("status").EntireColumn
    ' You can add many more fields... for sorting
    .Sort.Apply
End With

EDIT

To sort a field with a custom order, you need the custom order to be a comma-separated string:

Dim custom As String: custom = "L-2sd, L-1sd, P, U+1sd, U+2sd"
...
.Sort.SortFields.Add Key:=Range("D2:D10000"),..., CustomOrder:=custom
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

That is great, thanks A.S.H but I am still having trouble customising the order in the final column. Please see the below code, if you have any insight I would be most greatful
Hi @MrNemo. Please see the Edit for how to sort with a custom order.
Having a bit of an issue with that...I seem to be getting a type mismatch error? What am I doing wrong?????
solution: CVar(custom)
1

The suggestion worked nicely. I am still having issues personalising the order. My code:

Dim keyRange As Variant
Dim KeyRange1 As Variant
Dim sortNum As Long
Dim SortNum1 As Long
keyRange = Array("L-2sd", "L-1sd", "P", "U+1sd", "U+2sd")
KeyRange1 = Array("A", "D", "S", "T", "F")

 Application.AddCustomList ListArray:=KeyRange
sortNum = Application.CustomListCount
Application.AddCustomList ListArray:=KeyRange1
SortNum1 = Application.CustomListCount
With ThisWorkbook.Worksheets("Order")
.Sort.SortFields.Clear
.Sort.SetRange .Range("A1:" & Letter & 10000)
.Sort.SortFields.Add Key:=Range("A2:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("B2:B10000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("C2:C10000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
 DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("D2:D10000"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=SortNum1, DataOption:=xlSortNormal
 .Sort.SortFields.Add Key:=Range("E2:E10000"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=SortNum DataOption:=xlSortNormal


' You can add many more fields... for sorting
.Sort.Apply
 End With

I am just having trouble getting the last column to sort as I wish it to. Currently it just sorts in alphabetical now. I wish it to sort in the same order as the KeyRange

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.