+ Reply to Thread
Results 1 to 9 of 9

Sorting usedsheet with macro

  1. #1
    Registered User
    Join Date
    02-02-2005
    Posts
    11

    Sorting usedsheet with macro

    I am too new to excel vba to understand a lot of the code I see here which makes it very difficult to find what I need. I have a macro that when executed, sorts my sheet by cols a, b and c. I created it by using the recorder to sort those columns. The problem is that it comes bach with

    Please Login or Register  to view this content.
    which works, but only until I add cols or lines. When I add cols, the range of the sort fails to include the new columns because of the hard coding of col/line numbers. I can't figure out the syntax of how to have it dynamically sort the sheet based on its current used area.

    Any help would be appreciated.

    Thanks.

    PS Is there a way to have this forum notify me when this thread is updated? I haven't been able to figure that out either. Until I figure that out, if you would copy [email protected] with your reply, it would be much appreciated.
    Last edited by clearwaterdave; 09-27-2007 at 10:28 AM. Reason: complete breakdown of communications with VBANOOB

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Please read forum rules below and then wrap your code (Rule 5)

    To get a return e-mail select quick links across the top of the screen and Edit Options then look for Default Thread Subscription Mode

    VBA Noob
    Last edited by VBA Noob; 09-27-2007 at 06:59 AM.
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    02-02-2005
    Posts
    11
    Quote Originally Posted by clearwaterdave
    I am too new to excel vba to understand a lot of the code I see here which makes it very difficult to find what I need. I have a macro that when executed, sorts my sheet by cols a, b and c. I created it by using the recorder to sort those columns. The problem is that it comes bach with

    Range("A1:H345").Sort Key1:=Range("F2"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    which works, but only until I add cols or lines. When I add cols, the range of the sort fails to include the new columns because of the hard coding of col/line numbers. I can't figure out the syntax of how to have it dynamically sort the sheet based on its current used area.

    Any help would be appreciated.

    Thanks.

    PS Is there a way to have this forum notify me when this thread is updated? I haven't been able to figure that out either. Until I figure that out, if you would copy [email protected] with your reply, it would be much appreciated.
    Thank you for the courteous tip. That helped a lot. In the future I'll use the code wrap. Now all I need is a good tutorial on vba for Excel so I don't get drowned in the syntax.

  4. #4
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    I would recommend dynamically naming the range that is to be sorted, then use the range name in your code.

    See links for dynamic ranges:

    http://www.cpearson.com/excel/named.htm#Dynamic
    http://www.contextures.com/xlNames01.html#Dynamic
    http://www.ozgrid.com/Excel/DynamicRanges.htm

    Also, for the code, instead of
    Please Login or Register  to view this content.
    you would use something like
    Please Login or Register  to view this content.
    Using a dynamic range will allow the range to constantly change without you having to change the range in your procedure.

    HTH

    Jason

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You can define the range within the code

    Please Login or Register  to view this content.
    Or you can use ActiveSheet.UsedRange if the sheet only contains the table.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Thread now closed.

    PM if you agree to wrap code as per forum rules

    VBA Noob

  7. #7
    Registered User
    Join Date
    02-02-2005
    Posts
    11
    Quote Originally Posted by royUK
    You can define the range within the code

    Please Login or Register  to view this content.
    Or you can use ActiveSheet.UsedRange if the sheet only contains the table.
    I would prefer to use your last suggestion as it appears to be far simpler. How would I substitute that range with:?
    Please Login or Register  to view this content.
    Also, what is the significance of Key1:=Range("F2")? I haven't specified that anywhere, yet that's what the macro recorder gave me.

  8. #8
    Registered User
    Join Date
    02-02-2005
    Posts
    11
    Quote Originally Posted by VBA Noob
    Now open awaiting clearwaterdave to compile with rules by wrapping text. Please do not reply as your post will be deleted

    VBA Noob

    Am I missing something? Am I talking to a stone wall? Just how many times do I have to shout YES, I COMPILE (?) WITH RULES BY WRAPPING TEXT!

    Or are you just trying to intimidate me for some reason I have yet to understand?????

  9. #9
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    The VBA code on post one is still unwrapped

    Link to Post 1

    All code needs to be wrapped like

    Please Login or Register  to view this content.

    VBA Noob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1