sorting database with more than 1 keys in excel vba

Question

i want to sort a database with 4 keys but whenever am adding the 4th key its saying named argument not found whereas it sorting the database with these 3 keys.. how to add 4th one in this..??

Range("A1:E" & lastrow).Sort key1:=Range("A1:A" & lastrow), order1:=xlAscending, _
                             key2:=Range("C1:C" & lastrow), order2:=xlAscending, _
                             key3:=Range("D1:D" & lastrow), order3:=xlDescending, _
                             Header:=xlYes

Answer 1

The Range.Sort method has a maximum of three keys per process. However, if you sort by the extra keys first, then resort by the three most primary keys, you achieve the same results.

With Range("A1:E" & lastrow)
    'sort on the 4th key first (column E)
    .Cells.Sort Key1:=.Columns(5), Order1:=xlAscending, _
                Orientation:=xlTopToBottom, Header:=xlYes
    'sort on the 1st, 2nd and 3rd keys (columns A, C and D)
    .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                Key2:=.Columns(3), Order2:=xlAscending, _
                Key3:=.Columns(4), Order3:=xlAscending, _
                Orientation:=xlTopToBottom, Header:=xlYes
End With

This produces the same result as if there was a Key4:=.Columns(5), Order4:=xlAscending allowed in the method.

I try to use a With ... End With statement to ensure an easy method of defining the parent of the keys. Your original code relied upon the ActiveSheet property which is not always reliable.