Using VBA to Sort Multiple Columns

meclive · July 30th, 2008, 10:05 AM

Hi there, im using the code below to sort multiple columns when the worksheet is activated. This works, but is incredibly slow in calculating the cells! I was wondering if there was a faster way to do this?

Quote:

quote:Worksheets("lookup").Range("E33:F200").Sort _
        Key1:=Worksheets("Prov_lookup").Range("F33"), order1:=xlDescending, Header:=xlNo
Worksheets("lookup").Range("G33:H200").Sort _
        Key1:=Worksheets("Prov_lookup").Range("H33"), order1:=xlDescending, Header:=xlNo
Worksheets("lookup").Range("I33:J200").Sort _
        Key1:=Worksheets("Prov_lookup").Range("J33"), order1:=xlDescending, Header:=xlNo
Worksheets("lookup").Range("K33:L200").Sort _
        Key1:=Worksheets("Prov_lookup").Range("L33"), order1:=xlDescending, Header:=xlNo
Worksheets("lookup").Range("M33:N200").Sort _
        Key1:=Worksheets("Prov_lookup").Range("N33"), order1:=xlDescending, Header:=xlNo
Worksheets("lookup").Range("O33:P200").Sort _
        Key1:=Worksheets("Prov_lookup").Range("P33"), order1:=xlDescending, Header:=xlNo
Worksheets("lookup").Range("Q33:R200").Sort _
        Key1:=Worksheets("Prov_lookup").Range("R33"), order1:=xlDescending, Header:=xlNo
Worksheets("lookup").Range("S33:T200").Sort _
        Key1:=Worksheets("Prov_lookup").Range("T33"), order1:=xlDescending, Header:=xlNo
Worksheets("lookup").Range("U33:V200").Sort _
        Key1:=Worksheets("Prov_lookup").Range("V33"), order1:=xlDescending, Header:=xlNo
Worksheets("lookup").Range("W33:X200").Sort _
        Key1:=Worksheets("lookup").Range("X33"), order1:=xlDescending, Header:=xlNo

thanks.

allenm · July 30th, 2008, 11:04 AM

If you aren't already, try turning off calculation and display until the sorts are done:
Application.ScreenUpdating = FALSE
Application.Calculation = xlCalculationManual

Then turning them back on when done:
Application.ScreenUpdating = TRUE
Application.Calculation = xlCalculationAutomatic