Excel Sorting Using Vba, Avoid Duplicating Code?

I have written the following code which sorts by column h, then i, then j etc to u. But as you can see the code which says by which columns to sort by is repeated over and over. What I’m asking is if there’s a a way of doing this with less code.
Code:
Sub AutoRun()
RunTime1 = Now + TimeValue(“00:01:00”)
Application.OnTime RunTime1, “AutoRun”
Range(“a9:cu9”, ActiveSheet.Range(“a9”).End(xlDown)).Nam… = “SortArea”
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range(“H:H”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“I:I”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“J:J”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“K:K”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“L:L”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“M:M”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“N:N”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“O:O”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“P:P”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“Q:Q”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“R:R”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“S:S”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“T:T”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(“U:U”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(“SortArea”)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Both comments and pings are currently closed.

One Response to “Excel Sorting Using Vba, Avoid Duplicating Code?”

  1. Greywolf says:

    How about this, or something a bit like?
    First it sorts, then it renumbers te rows:
    Range(“B4:M99”).Select
    Selection.Sort Key1:=Range(“B4”), Order1:=xlAscending, Key2:=Range(“C4”) _
    , Order2:=xlAscending, Key3:=Range(“D4”), Order3:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range(“A4:A99”).Select
    ActiveWindow.ScrollRow = 1
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
    Step:=1, Stop:=100, Trend:=False

Powered by WordPress | Designed by: free css template | Thanks to hostgator coupon and web hosting reviews