Use a macro to sort a user selected range

[rmccafferty]

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?

 

[ColinKJ]

Hi rmccafferty,

I told you:

I'm sure there are many out there on the forum who can write a more concise code

.

If you apply wigi's code to your requirements:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With Selection
If .Address = .EntireRow.Address Then
Selection.Sort Key1:=Range("AJ" & .Cells(1).Row), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Cancel = True
End If
End With
End Sub

It looks far better and does the same thing.

And I'm sure there's someone out there that can make it look even more attractive.

Thanks for your code wigi

 

[rmccafferty]

Personally, the interchange going on is very informative to me. I learn so much more here than from books.

Robert