Results 1 to 10 of 10
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Flaky Sort Macro - Excel 2003

    My SortHotelsByEntity macro worked fine at one time. It started misbehaving and I tweaked it. It has never been the same.

    There is a Header row in $A8. I want the named range "AllHotels" to include it, which it doesn't right now.

    I want this macro to sort the named range "AllHotels", always keeping the Header row at the top. (When it misbehaved, it sorted the Header row with all the other data.)

    Sub SortHotelsByEntity()
    ' Macro recorded 5/28/2012 by Louis F. Sander
    '
    Application.Goto Reference:="AllHotels" ' AllHotels=Hotel!$A$9:$BA$65536
    Selection.Sort Key1:=Range("I9"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("I9").Select
    End Sub

    Who can tweak the macro so it does what I want? (I'm reluctant to touch it, since I've already mess things up a bit by doing so.)

    PS - I seem to recall that there was a way to include macro code in a post without losing its formatting. Is this still possible?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I hope this isn't a silly answer, but I would have thought it simplest to re-record the Macro, using the options (including the Header Row) you want - as you describe. That will be more foolproof than tinkering at long range, I think.

    If you need help with tuning the newly-recorded macro -post back.

    As for including code, just highlight it in your post and click on the button marked with a # - hover over it and you'll see "Wrap [CODE] tags around selected text".

    You cannot do this in a Quick Reply - you have to Go Advanced.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts

    Sort leaving header

    Lou,
    Try this code and see if it solves your problem assuming you have already named the range Hotel!$A$8:$BA$65536 to AllHotels.
    HTH,
    Maud

    Code:
    Public Sub SortHotelsByEntity()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("AllHotels"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("AllHotels")
        .Header = xlYes
        .Apply
    End With
    End Sub
    Last edited by Maudibe; 2013-02-05 at 19:41. Reason: included the header in the range

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Lou

    This will do what you asked for:

    Code:
    Sub SortHotelsByEntity()
    
    [AllHotels].Sort Key1:=[i9], Order1:=xlAscending, Header:=xlYes, _
       OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
       DataOption1:=xlSortNormal
       
    [i9].Select
     
    End Sub
    zeddy

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Martin

    You can include code in a Quick Reply without hovering over a # and stuff.
    You just start by typing on a new line, left-square bracket [ then the word code then the right square bracket ]
    ...then paste your
    sub() code
    xxx
    end sub
    ..then finish with left-square bracket [ forward slash /code]

    zeddy

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    True Zeddy - I'm just lazy I suppose !

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Martin

    I believe it is me that's lazy!
    I just can't remember doing it the other way!

    zeddy

  8. #8
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I'm still a bit confused here. I have a need to have the AllHotels range include row 8 as well as row 9 (because I use it in a mail merge document). The solutions advanced above either don't mention it, or talk about renaming it to include row 9.

    I THINK I just need to rename AllHotels to include that row, but I'm not certain about it and I don't want to dig my hole any deeper.

    I'm NOT lazy -- just gun-shy.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Lou,

    The code on post #3 was under the assumption that the header was included in the range (even though I wrote$A$9 instead of $A$8). Zeddy's code includes it as well. Note the line:

    .Header = xlYes

    If you want a little security, make a copy of the sheet and send it to the end. Place the code in the Copied sheet's module in the vb editor and run it. If any code is not to your satisfaction, you will not be touching the original and your hole will be no deeper. If it does what you want, transfer the code to the original's sheet then delete the copy.

    HTH,
    Maud
    Last edited by Maudibe; 2013-02-05 at 19:44.

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Lou

    You are correct.
    You just need to redefine your AllHotels to include row 8.
    Then use my code in post#4

    zeddy

Posting Permissions

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