Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sorting / Dynamic Range? (XL2003)

    I have a data list on a spreadsheet. I have a macro button that sorts (by Column A). My data starts in cell A6. The macro button works fine but I have to continually change it to include the latest additions to the data. The data list grows by several records each week.

    I know there's a way to do away with i.e. (A6:J100) and replace that with a range name. But I don't know exactly how to replace that with a range name that changes on it's own to include new records. I believe it involves dynamic ranges...

    Can someone offer guidance in setting this up and incorporating it into a sort macro.
    - Ricky

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting / Dynamic Range? (XL2003)

    Ricky,

    Try this:
    Example
    Assuming there are no blank cells in C5 to C "end" you can create a dynamic range name with OFFSET:

    Define a named range (insert - name define)
    CountC
    that refers to (no quotes):
    "=COUNTA(Sheet1!$C:$C)-COUNTA(Sheet1!$C$1:$C$4)"
    This will count the entries in Col C
    Then create another named range:
    LookupArray [or whatever you want to name it]
    that refers to (no quotes)
    "=OFFSET(Sheet1!$C$5,0,0,CountC,12)"

    This range will be [C5:Nx] where x will grow as new items are added to col C. As the items in C increases, the range will automatically expand.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting / Dynamic Range? (XL2003)

    To use a named range, use this syntax:

    Workbooks("YourBook.xls").Names("YourName").Refers ToRange
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting / Dynamic Range? (XL2003)

    Thanks a lot Chuck...

    I've applied the information you gave and it worked great but it I do have a follow-up question: Since I know my data begins in Row 6 (specifically A6), is it okay to change your example from
    <pre>"=COUNTA(Sheet1!$A:$A)-COUNTA(Sheet1!$A$1:$A$5)"</pre>

    to
    <pre>"=COUNTA(Sheet1!$A:$A)-5)"</pre>


    I've already done this and it appears to work the same. I'm just wondering if there's a reason I should not do this?? Will it likely cause a problem later that I'm not aware of?

    Thanks again,
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting / Dynamic Range? (XL2003)

    It will cause a problem if any of the cells A1:A5 are blank...

    Steve

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting / Dynamic Range? (XL2003)

    Thanks Steve - I changed it back.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting / Dynamic Range? (XL2003)

    You must also pay attention to Chuck's comment.

    The method assumes that from A6 to the last occupied cell in A, that there are no blanks. If there are any blanks, this method will not work.

    Steve

Posting Permissions

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