• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 842
  • Last Modified:

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

0
marknlynn3
Asked:
marknlynn3
1 Solution
 
Berkson WeinTech FreelancerCommented:
Here's something that should help:
Sub DoSort(sSheetName As String, sColToSortOn As String, _
        sStartCol As String, sEndCol As String, _
        iStartRow As Long, iEndRow As Long)

    ActiveWorkbook.Worksheets(sSheetName).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(sSheetName).Sort.SortFields.Add _
        Key:=Range(sColToSortOn & iStartRow & ":" & sColToSortOn & iEndRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(sSheetName).Sort
        .SetRange Range(sStartCol & iStartRow & ":" & sEndCol & iEndRow)
        .Header = False
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


Sub TestIt()

    DoSort "Sheet1", "A", "A", "AK", 1, 5
    DoSort "Sheet1", "A", "A", "AK", 6, 19
    DoSort "Sheet1", "A", "A", "AK", 20, 25

End Sub

Open in new window


I've made is generic so that you can specify:
The Sheet Name
The column you want to sort on
The starting column
The ending column
and then specify the starting and ending row


For only 600 iterations, I wouldn't think it would be be any noticible speed impact using subroutine calls.   You could hard code the column letters, which column to sort on, and the sheet name if you really wanted, but this code should be fast enough without doing that.

Hope this is a good start.  
0
 
marknlynn3Author Commented:
This looks great.  Thanks much.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now