Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    How to do a last column sort?

    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!
    Beryl M


  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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!"
    Beryl M


  4. #3
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 169 Times in 165 Posts
    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

  5. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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!
    Beryl M


  6. #5
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 169 Times in 165 Posts
    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

  7. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.
    Beryl M


  8. #7
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 169 Times in 165 Posts
    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

  9. The Following User Says Thank You to zeddy For This Useful Post:

    BerylM (2012-03-10)

  10. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you, Zeddy, that's great!
    Beryl M


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •