Can this excel sorting macro be made to work on any selected range?

October 20, 2011 7:42 AM

Inside is a recorded excel macro, designed to sort a specific range of cells on a specific worksheet from smallest to largest, from left to right, using the values in a specific range of cells in a row. Is there a way to edit this macro so that it will do the same sort on any selected group of cells on any worksheet?

Basically I get automatically generated workbooks of data on a regular basis. In order to compare the data from two different worksheets I sort them by a row that has consistent values across different workbooks (though the range of cells is not always the same from sheet to sheet). So below, the values in the range C6 to K22 on worksheet 15 are sorted left to right by the values in cell C6 to K6 when the macro runs. The sort row is always the top row of the range (and is always row 6 here, though that won't always be the case).

What I would like to do is make this macro usable for any selected range on any worksheet, using the values in the top row of the range. For example, on sheet 16, the range would be C6 to L15, sorted on the values in C6 to L6; on sheet 17 the range would be C6 to G25 sorted left to right on C6 to G6; and so on. Is this possible, or do I need separate macros that define the specific range to be sorted for each worksheet? It's not an unmanagable load of manual work currently, but it would be great if I could automate it.

Macro follows:

Sub left_to_right()
'
' left_to_right Macro
' macro for sorting tables left to right on row 6
'

'
Range("C6:K22").Select
ActiveWorkbook.Worksheets("15").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("15").Sort.SortFields. _
Add Key:=Range("C6:K6"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("15").Sort
.SetRange Range("C6:K22")
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub

posted by Lentrohamsanin to Computers & Internet (2 answers total)

Best answer: You can replace this: ActiveWorkbook.Worksheets("15") with just ActiveSheet to get the sheet currently on top.

And your currently selected range is referenced by Selection. Within a Selection, you have Rows and Columns. So you can take out the line Range("C6:K22").Select and just replace .SetRange Range("C6:K22") with Selection, I believe.

I'm not quite 100% sure what your code does, but if you want to send me a sample workbook, I'd be glad to test a solution for you.
posted by SuperSquirrel at 3:27 PM on October 20, 2011

Best answer: Here's a generic version that is still very close to the original

Sub left_to_right_generic()
'
' left_to_right Macro
' macro for sorting tables left to right on row 6
'

'
Dim sortRange As Range

Set sortRange = Selection

ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields. _
Add Key:=sortRange.Resize(1, sortRange.Columns.Count), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub

1) A range variable has been added to hold the currently selected table. Possibly overkill in this instance but it keeps things clear and might be useful if further development is needed
2) The specific sheet name has been replaced with ActiveSheet.Name
3) The range to sort on is derived from the currently selected table by using the resize property to choose only the top row
posted by MUD at 3:33 PM on October 20, 2011