Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Trigger A to Z sorting without knowing the exact starting row...

    Hi guys,

    I am hoping this should be an easy one for someone:

    I have a trigger that works for A to Z sorting of my sheet but this time I don't know the exact row number so I am stuck with:

    r = Target.Row
    [fr:w47].Sort key1:=[f2], Header:=xlYes, Order1:=xlAscending

    [fr:w47] is where I am having problems... column is "f" and the end is constant so "w47" is good...

    Thanks in advance...
    Last edited by Ferenc Nagy; 2015-06-25 at 05:42.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    This should work.
    Code:
       Dim lStartRow As Long
       Dim lEndRow   As Long
        
       lStartRow = ActiveCell.End(xlUp).Row
       lEndRow = ActiveCell.End(xlDown).Row
    
    
        Range("F" & Format(lStartRow, "#") & ":W" & Format(lEndRow, "#")).Sort key1:=[F2],  _
                         Header:=xlYes, Order1:=xlAscending
    Of course I don't know what your trigger is and I'm assuming that the data table has NO blank entries. This is why a sample sheet is always a good idea when requesting help. Or at least your whole macro.

    HTH
    Last edited by RetiredGeek; 2015-06-25 at 09:22.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Ferenc Nagy (2015-06-25)

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI, you really don't need the Format there:
    Code:
    Range("F" & lStartRow & ":W47")
    will do. I suspect the original code needs to alter the sort key range too.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    Ferenc Nagy (2015-06-25)

  6. #4
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Ferenc,

    This should work.
    Code:
       Dim lStartRow As Long
       Dim lEndRow   As Long
        
       lStartRow = ActiveCell.End(xlUp).Row
       lEndRow = ActiveCell.End(xlDown).Row
    
    
        Range("F" & Format(lStartRow, "#") & ":W" & Format(lEndRow, "#")).Sort key1:=[F2],  _
                         Header:=xlYes, Order1:=xlAscending
    Of course I don't know what your trigger is and I'm assuming that the data table has NO blank entries. This is why a sample sheet is always a good idea when requesting help. Or at least your whole macro.

    HTH
    I should have thought of including the file but it did work...

    FYI, you really don't need the Format there:
    Code:
    Range("F" & lStartRow & ":W47")

    will do. I suspect the original code needs to alter the sort key range too.

    Thank you for the shorter version...

  7. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Ferenc Nagy View Post
    FYI, you really don't need the Format there:
    Code:
    Range("F" & lStartRow & ":W47")
    will do.
    Sorry, old programming habits (45 years worth) to show steps explicitly, i.e. the Explicit Cast using Format vs just letting VBA cast the numeric value when concatenated onto a string.

    Glad it solved your problem.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I have 1 more problem, very similar but with a twist.

    Code:
            Dim lStartRow As Long                               'sort the tab alphabetically from the deleted row to the end..
            Dim lEndRow   As Long
        
            lStartRow = ActiveCell.End(xlUp).Row
            'lEndRow = ActiveCell.End(xlDown).Row
    
    
            Range("M" & lStartRow & ":W47").Sort key1:=[F2], Header:=xlYes, Order1:=xlAscending
    The starting row this time is the last row + 1 of column F...

    Could you help me how to modify the StartRow please...

  9. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    This should do it:
    Code:
           lEndRow = ActiveCell.End(xlDown).Row
    
    
            Range("M" & lEndRow+1 & ":W47").Sort key1:=[F2], _
                                            Header:=xlYes, _
                                            Order1:=xlAscending
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #8
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Ferenc,

    This should do it:
    Code:
           lEndRow = ActiveCell.End(xlDown).Row
    
    
            Range("M" & lEndRow+1 & ":W47").Sort key1:=[F2], _
                                            Header:=xlYes, _
                                            Order1:=xlAscending
    HTH

    [F2] needed to be changed to [M2] but I figured that out quickly.

    Thank you very much for the help!

Posting Permissions

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