We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Concise VBA Sort Code

marknlynn3
marknlynn3 asked
on
Medium Priority
887 Views
Last Modified: 2012-08-14
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

Comment
Watch Question

Tech Freelancer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This looks great.  Thanks much.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.