CODEWITHSUNDEEP
vbaexcelsorting
user6089076
user6089076

Reputation:

Custom Sort on Range VBA

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

Upvotes: 2

Views: 5201

Answers (2)

A.S.H
A.S.H

Reputation: 29332

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

Upvotes: 3

user6089076
user6089076

Reputation:

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

Upvotes: 1

Related Questions