Use a macro to sort a user selected range

rmccafferty · April 21st, 2011, 05:52 PM

I would like to have the user select a group of data (full rows). (Some number of lines that will be different each time). Then push a button to sort the rows. I recorded a macro first, thinking that I could then adjust the code as necessary.

My problem is that the recorded macro gives the specific range of rows to sort. I want it to sort whatever set of rows the use may have selected at the time the macro is run.

The code I ended up with is:

'ActiveWorkbook.Worksheets("Pugh").Sort.SortFields .Clear
' ActiveWorkbook.Worksheets("Pugh").Sort.SortFields. Add 'Key:=Range("AJ28:AJ33") _
' , SortOn:=xlSortOnValues, Order:=xlDescending, 'DataOption:=xlSortNormal
' With ActiveWorkbook.Worksheets("Pugh").Sort
' .SetRange Range("A28:CN33")
' .Header = xlGuess
' .MatchCase = False
' .Orientation = xlTopToBottom
' .SortMethod = xlPinYin
' .Apply
' End With

The SetRange line is the one I don't know what to do with. How can I run this macro and tell it to just use whatever rows are selected on screen at that moment?

mtranchi · April 24th, 2011, 01:15 PM

Code:

Public Sub sSortSelection()

'use the keyword "Selection" for the currently selected range
With ActiveSheet.Sort
    .SortFields.Clear
    'the key you want to use is the column to sort on. I used column 1, which is "A", column "B" is 2, etc
    .SortFields.Add Key:=Selection.Columns(1), Order:=xlDescending
    .SetRange Selection
    .Apply
End With

End Sub

rmccafferty · April 25th, 2011, 08:47 PM

Thank you very much.

Clearly, what I did not get was the
.SetRange Selection
statement

Will that work any time I want to perform an action on a range and have selected the range on screen?

mtranchi · April 26th, 2011, 04:32 PM

you're welcome, glad to be of help. yes, as far as i know Selection always refers to whatever is selected on screen. I don't make much use of graphs, pictures, drawings (shapes), so dunno about that, but if a range or a single cell is selected, that's your ticket. :)