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,

What column do you want the sort based on?

 

[rmccafferty]

Column AJ

 

[wigi]

Perhaps:

.SetRange Selection.EntireRow

 

[ColinKJ]

Hi rmccafferty,

Sorry It's taken so long to get back to you, Easter & all that.

This code should do you:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
a = ActiveWindow.RangeSelection.Address
For b = 1 To Len(a)
If Mid$(a, b, 1) = "$" Then GoTo nextb
If Mid$(a, b, 1) = ":" Then GoTo nextb
If IsNumeric(Mid$(a, b, 1)) = False Then Cancel = True: GoTo endd
nextb:
Next b
st = 0: en = 0: y = 0
For y = 1 To Len(a)
If Mid$(a, y, 1) = "$" Then st = y + 1
If Mid$(a, y, 1) = ":" Then en = y
If en <> 0 Then r = Val(Mid$(a, st, en - st)): GoTo x
nextbb:
Next y
Stop
x:
Selection.Sort Key1:=Range("AJ" & r), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Cancel = True
endd:
End Sub

This code sits in the Private Sub Worksheet_BeforeRightClick.

If you select whichever rows you want, then Right Click, the data in the selected rows will be sorted based on Column AJ.

 

[rmccafferty]

Wow, I am impressed.

I can usually figure out the code of other people once I see it, but I really do not understand some of this and can't wait to see how it works.

One question: Can I assume that the line

a = ActiveWindow.RangeSelection.Address

Sets the range for the sort? That was my real stumbling block, from the beginning, how to set the range for the sort. (Not that I could have developed anything of the complexity you posted).

I though that if I could just set the range, I could then use the code Excel generates for the sort itself. That is, I could hand select a range of rows, Let Excel record my keystrokes as I performed the sort. And then use the Excel generated code preceded by whatever code line I needed to set the range for the sort to equal what was selected on screen.o perform a sort. That is, record my keystrokes and then use that code (which is in my original post) and precede it with whatever it takes to set the range for the sort as the rows selected on screen by the user.

 

[ColinKJ]

Hi rmccafferty,

You are correct about a = ActiveWindow.RangeSelection.Address.

The reason for all the code, has very little to with the sort aspect, but more to manage the Right Click Function.

It should only do the Sort if there is a rang of rows selected, but not if it's a single cell, or rang of cells.

Also, part of the code works out the start row for the sort.

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

 

[wigi]

ColinKJ

Can you look at this code please?

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    With Selection
        If .Address = .EntireRow.Address Then
            MsgBox "entire row(s) selected, row " & .Cells(1).Row & " is the first row"
        Else
            MsgBox "no entire row(s) selected"
        End If
    End With
    Cancel = True
End Sub

Wigi

 

[ColinKJ]

Hi wigi,

I've looked at the code and it appears to work but I'm not sure what you want it to do ?.

It's more concise than my previous code.

 

[wigi]

Hi wigi,

I've looked at the code and it appears to work but I'm not sure what you want it to do ?.

It's more concise than my previous code.

Hello Colin,

Yes, much more concise.

What in fact is the purpose of the first 2 code blocks in your suggestion? With all the loops and the GoTo's? GoTo's can lead to spaghetti-code...