Sorting on more than 3 columns

[kwill]

Can you sort on more than 3 columns in excel VBA? This is what I'm trying and it's not working.

Selection.Sort Key1:=Range("E2&quot, Order1:=xlAscending, Key2:=Range("K2&quot _
, Order2:=xlAscending, Key3:=Range("O2&quot, Order3:=xlAscending, Key4:=Range("h2&quot, order4:=xlAscending, key5:=Range("I2&quot, order5:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

 

[RobBroekhuis]

According to Excel help, three keys is all you get. The easiest way to get around this is to create a (temporary) extra column in which the values you're sorting on are concatenated (with appropriate padding), and use that as one of your keys.
Something like:

for i=3 to LastRow
   s=left(cells(i,15)+"                   ",20)
   s=s+left(cells(i,8)+"                   ",20)
   s=s+left(cells(i,9)+"                   ",20)
   cells(i,26)=s   'put values in Z column
next i

(now sort on keys E, K, Z)
(now you can delete column Z)

You'll have to adjust the code a little bit if some of the values are numeric.
Rob

 

[kwill]

Good point, that will work.