How to do a last column sort?

Topic

New Reply

Is there any way to write a macro that would tell XL to just sort by the last column of the current selection, largest to smallest?I do get so fed up with having to set up the sort, telling it which column and changing to largest to smallest every time when we have literally dozens of selections to do this to …!Any help would be gratefully and greatly appreciated!

Reply | Quote

Replies

I keep trying to edit the above post to make my request clearer, but it won’t accept any changes, so herewith the altered version separately: “Is there any way to write a macro that would tell XL to just sort by the last column of the current selection (ie far right column, whether fourth of four, eighth of eight, or whatever), and largest to smallest? I do get so fed up with having to set up the sort, telling it which column and changing to largest to smallest every time when we have literally dozens of selections to do this to …! Any help would be gratefully and greatly appreciated!”

Reply | Quote

Hi Beryl

This could be done with VBA but do you need to?
If you are using Excel2003(?) you could simply add the Descending Sort icon to your toolbar.
That way, when you have a currentRegion block of data, you simply put the cellpointer in the column you want sorted and click the descending-sort icon.

If you want a VBA solution, post a reply and let us know which Excel version you are using.

zeddy

Reply | Quote

Thank you, Zeddy, I think that will do what I want! One question, though – how do you “put the cellpointer in the column you want sorted”? I did it by starting the selection in that column (which for my purposes at this moment, sorting always on the last column, works fine) but what if I was selecting several columns and I wanted to sort by one of the middle ones – how do I put the cell pointer in that column? When I try I just lose the selection! By the way, how does one put a blank line in one of these posts now?!! You used to just press return twice as per a wordprocessor, but I notice that it now runs them together (as you’ll probably see with this para, which will no doubt run into the previous one even though I’ve put returns between them!Thanks again!

Reply | Quote

Hi Beryl

If you are only sorting on ONE column (as per your first post), then you just need the cellpointer on a data cell anywhere in that column (e.g. on the column header preferably). The sort will then ‘grab’ the current region around that selected cell and do the sort.

If you want to sort on more than one column at the same time (e.g. sort this column first then this column etc) then you have to use the Data Sort option from the top-panel command bar. The cellpointer can be anywhere in the data block to be sorted. When you select Data and then Sort, the current region is selected automatically and you will see the sort options dialog sheet. This dialog sheet allows you to specify the order of your sorts (i.e. which data columns) and sort type, for up to three columns.

Re: how does one put a blank line in one of these posts now?!!p
As you stated, I press return twice as per a wordprocessor.
Works for me!

zeddy

Reply | Quote

Hi Zeddy, slight misunderstanding here – I need to be able to select a block of cells, several columns by several rows – which will not necessarily be the current region as we have columns of formulae to the right of the data cells that are not to be touched by the sort – then sort by just one column within that block. Re putting blank lines, mine simply doesn’t, as you can see from the earlier posts (and probably this one) – I’ve been trying to remember to put spaces at the end of the paras so when it runs them together it doesn’t do so quite literally, but I’d much rather they stayed as separate paras! Thanks for your help.

Reply | Quote

Hi Beryl

Well I suppose I had better answer your first post exactly as you require.
It’s just that I think sorting a ‘selected’ range (rather than a ‘current region’) can often do more harm than good.
Sorting any block of data, particularly when the selected ‘block’ lies within another ‘block’, can often ‘mess up’ data records.
But never mind that, here is a VBA routine that will specifically sort the last column of a selected range in descending order. You could assign this routine to a ‘custom button’ in your toolbar:

Code:

Sub sortSelectionLastColumn()
If Selection.Rows.Count = 1 Then Exit Sub
If Selection.Cells.Count = 1 Then Exit Sub
zLastCell = Selection.Find(What:="*", _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Address
Selection.Sort Key1:=Range(zLastCell), Order1:=xlDescending, Header:=xlGuess
End Sub

zeddy

Reply | Quote

Thank you, Zeddy, that’s great!

Reply | Quote