Link to home
Start Free TrialLog in
Avatar of marknlynn3
marknlynn3Flag for United States of America

asked on

Concise VBA Sort Code

In my Excel 2010 VBA code I routinely have to sort the rows in a table.  The table has many subheadings, so the code needs to sort groups of rows in the table separately.  For example I might need to sort rows 7-10, 13-21, 23-26, etc all in descending order by the values in column K.  I currently use the macro recorder to generate the sort code, but end up with pages of code for what is conceptually a very simple process.  I am looking for some help in shortening the code either by removing unneeded information added by the macro recorder or creating a subroutine I can call with the row numbers to be sorted passed as arguments.  The worksheet containing the table and the column to sort by could be hard-coded into the subroutine or passed as arguments.  For example: Call SortRow(7,10).  I loop through this code about 600 times so I'm not sure if there would be a substantial impact on speed due the overhead of calling a subroutine.
Rows("7:10").Select
    ActiveWorkbook.Worksheets("DetailedTable_Work2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DetailedTable_Work2").Sort.SortFields.Add Key:= _
        Range("K7:K10"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("DetailedTable_Work2").Sort
        .SetRange Range("A7:AK10")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Rows("13:21").Select
    ActiveWorkbook.Worksheets("DetailedTable_Work2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DetailedTable_Work2").Sort.SortFields.Add Key:= _
        Range("K13:K21"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("DetailedTable_Work2").Sort
        .SetRange Range("A13:AK21")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Rows("23:26").Select
    ActiveWorkbook.Worksheets("DetailedTable_Work2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DetailedTable_Work2").Sort.SortFields.Add Key:= _
        Range("K23:K26"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("DetailedTable_Work2").Sort
        .SetRange Range("A23:AK26")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Berkson Wein
Berkson Wein
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of marknlynn3

ASKER

This looks great.  Thanks much.