CODEWITHSUNDEEP
vbaexcel
tanzilamohita
tanzilamohita

Reputation: 107

How to sort more than 3 columns in VBA?

I want to sort more than 3 columns in VBA. When I add key4, it shows "Named argument not found." How can I handle this? Here is my code:

Private Sub SubLieutenant_Click()
Dim LastRow As Long


LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("F6:F" & LastRow).Formula = "=DateDif(C6, Today(),""y"")+DateDif(E6, Today(), ""d"")/30"

Range("A6").Sort key1:=Range("F6"), order1:=xlDescending, key2:=Range("E6"), order2:=xlAscending, key3:=Range("D6"), order3:=xlAscending, key4:=Range("C6"), order4:=xlAscending, Header:=xlYes

End Sub

Upvotes: 1

Views: 6985

Answers (2)

dbmitch
dbmitch

Reputation: 5386

Your example doesn't make much sense as it's sorting one cell?

But for a real example you just select the actual range first
then add the sort keys separately instead of in one line

With ActiveSheet
   .Sort.SortFields.Add Key:=Range("F2:F6"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
   .Sort.SortFields.Add Key:=Range("E2:E6"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   .Sort.SortFields.Add Key:=Range("D2:D6"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   .Sort.SortFields.Add Key:=Range("C2:C6"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With

With ActiveSheet.Sort
    .SetRange Range("A1:G6")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Upvotes: 2

Aditya Pansare
Aditya Pansare

Reputation: 1132

use below mentioned lines for sorting. Sort the data twice on different columns.

Range("A6").Sort key1:=Range("C6"), order1:=xlAscending, Header:=xlYes

Range("A6").Sort key1:=Range("F6"), order1:=xlDescending, _
        key2:=Range("E6"), order2:=xlAscending, _
        key3:=Range("D6"), order3:=xlAscending, Header:=xlYes

Upvotes: 0

Related Questions