• How to do a last column sort?

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » How to do a last column sort?

    Author
    Topic
    WSBerylM
    AskWoody Lounger
    March 2, 2012 at 2:12 am #481943

    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
    Viewing 2 reply threads
    Author
    Replies
    • WSBerylM
      AskWoody Lounger
      March 2, 2012 at 2:22 am #1322859

      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
    • zeddy
      AskWoody_MVP
      March 2, 2012 at 4:41 am #1322874

      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
      • WSBerylM
        AskWoody Lounger
        March 5, 2012 at 3:58 am #1323294

        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
        • zeddy
          AskWoody_MVP
          March 5, 2012 at 9:29 am #1323317

          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
          • WSBerylM
            AskWoody Lounger
            March 6, 2012 at 2:14 am #1323431

            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
            • zeddy
              AskWoody_MVP
              March 6, 2012 at 5:46 am #1323467

              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
    • WSBerylM
      AskWoody Lounger
      March 10, 2012 at 7:24 am #1324433

      Thank you, Zeddy, that’s great!

      Reply | Quote
    Viewing 2 reply threads
    Reply To: How to do a last column sort?

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.