VBA Sort with user sort column selection

[Peter Davison]

I have the following code that sorts a range of columns with a dynamic number of rows.
What I would like to do is allow the user to be able to select the sort column (e.g. CS20) or their column choice form row 20 to as long as the list is.
I can't work out in my mind how to get the user to be able to choose?
I'd appreciate your help.
Thanks

Range("CA20").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Sort key1:=Range("CS20", Range("CS20").End(xlDown)), _

order1:=xlAscending, Header:=xlNo

 

[ExcelGzh]

I suggest that the user clicks a cell in the column they want to sort. The code then uses the column property of the ActiveCell to set the sort key.

 

[Peter Davison]

Thank you for getting back to me.
I think I've sorted with another method (perhaps a bit long winded though)
I created a macro button as the column title with the macro assigned to that column.
So the user just clicks the column heading to sort whichever column they need.
Cheers

 

[ExcelGzh]

Yes, I used that solution for a spreadsheet I had but discovered it gave me ongoing maintenance issues if I wanted to make modifications to the spreadsheet. Copying from one part of the sheet to another would result in buttons being duplicated and adding additional columns meant having to create fresh links between the new buttons and the macro. If you're not intending to make changes to the spreadsheet and you're going to be the only person who ever uses the spreadsheet then you'll probably be OK, but otherwise you might need to consider a solution that is self-organising and won't give the people who come after you any grief.

 

[mikerickson]

I've used a "double click on column to sort on that column" as an interface. It works best when you are sorting the whole sheet, otherwise the maintenance (i.e. adding columns to the data table) can be an issue.

Another approach would be to convert the range into a Table.

 

[Peter Davison]

I'll take a look at that option.
Thank you both for all your help.

Regards