VBA Sort with user sort column selection

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
189
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 4, 2020
Messages
189
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,012
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.
 
Solution

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
189
Office Version
  1. 365
Platform
  1. Windows
I'll take a look at that option.
Thank you both for all your help.

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,565
Members
418,140
Latest member
ahepple86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top