marknlynn3
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER