Sorting data using R1C1 in a spreadsheet 2

[MrMajik]

I have a spreadsheet that I added a command button to. When you click the button I want the VB code to select two columns wide and ten rows deep and then sort them. So far pretty easy stuff...

Now comes the tricky part. How do I get the code so it is dynamic to the cursor location? For example, my spreadsheet tracks golf scores for ten golfers. There are four outings per month. I want to be able to select the first cell that has data for that golf outing and click the button and have it sort the ten players by best score to the poorest score.

Because I do not know exactly how many outings will happen each summer I only want one button for all of this.

Any suggestions?

Thank you.

 

[PaulTEG]

There's a much quicker & tidier way, there always is

a = ActiveCell.Row
b = ActiveCell.Column
If b \ 26 > 1 Then
b1 = (b \ 26)
Else
b1 = 0

End If
b = (b Mod 26)

If Not (b1 = 0) Then
col1 = Chr(b1 + 64) & Chr(b + 64)
Else
col1 = Chr(b + 64)
End If

firstcell = col1 & a
secondcell = Chr(Asc(col1) + 2) & a + 10
range(firstcell & ":" & secondcell).Select

End Sub


Not tested extensively, but it'll definitely work if you stay within the first 26 columns

 

[dwilson01]

This works if the persons name is in the active cell and the score is in the next column to the right.

Rng = ActiveCell.Address & ":" & ActiveCell.Offset(9, 1).Address
Range(Rng).Select
Selection.Sort key1:=Range(ActiveCell.Offset(0, 1).Address)