Closed Thread
Results 1 to 7 of 7

Update Named Ranges

  1. #1
    Raul
    Guest

    Update Named Ranges

    I have a number of named ranges in a workbook, and these ranges need to be
    updated as new data is added. Is there a way to select all the named ranges
    in a workbook and read the contents in the "refers to:" field? As an aside,
    could these ranges be set up to be dynamic so they will update themselves to
    fit the data?

    Thanks in advance,
    Raul



  2. #2
    Bob Phillips
    Guest

    Re: Update Named Ranges



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Raul" <[email protected]> wrote in message
    news:[email protected]...
    > I have a number of named ranges in a workbook, and these ranges need to be
    > updated as new data is added. Is there a way to select all the named

    ranges
    > in a workbook and read the contents in the "refers to:" field? As an

    aside,
    > could these ranges be set up to be dynamic so they will update themselves

    to
    > fit the data?
    >
    > Thanks in advance,
    > Raul
    >
    >




  3. #3
    Tom Ogilvy
    Guest

    Re: Update Named Ranges

    change refers to to something like

    =Offset(Sheet1!$A$1,0,0,counta(Sheet1!$A:$A),8)


    the pseudo code for looping names is

    Dim nme as name, rng as Range
    for each nme in Thisworkbook.Names
    on error resume next
    set rng = nme.ReferstoRange
    on Error goto 0
    if not rng is nothing then
    msgbox nme.name & " refers to " & rng.Addess(external:=true)
    else
    msgbox nme.name & " refers to " & nme.refersto
    end if
    Next

    --
    Regards,
    Tom Ogilvy

    "Raul" <[email protected]> wrote in message
    news:[email protected]...
    > I have a number of named ranges in a workbook, and these ranges need to be
    > updated as new data is added. Is there a way to select all the named

    ranges
    > in a workbook and read the contents in the "refers to:" field? As an

    aside,
    > could these ranges be set up to be dynamic so they will update themselves

    to
    > fit the data?
    >
    > Thanks in advance,
    > Raul
    >
    >




  4. #4
    Bob Phillips
    Guest

    Re: Update Named Ranges

    Raul,

    Named ranges is the way to go.

    To do this, you use OFFSET, specifyhing the first cell, and the number rows
    and columns. Usually, it is rows that expand, so it is that bit that is
    dynamic, so COUNTA is used count the cells for the dynamic part.

    For instance

    =OFFSET($A$1,,,COUNTA($A:$A),3)

    will provide a range that is 3 columns wide, starting at A, and extending
    down however many rows there are in A. Just put this in the RefersTo field.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Raul" <[email protected]> wrote in message
    news:[email protected]...
    > I have a number of named ranges in a workbook, and these ranges need to be
    > updated as new data is added. Is there a way to select all the named

    ranges
    > in a workbook and read the contents in the "refers to:" field? As an

    aside,
    > could these ranges be set up to be dynamic so they will update themselves

    to
    > fit the data?
    >
    > Thanks in advance,
    > Raul
    >
    >




  5. #5
    Bill Foster
    Guest

    RE: Update Named Ranges

    How about either

    making the range name equal to all the 65,000+ rows in the applicable columns

    or add one row extra to the range names and have the new data added in to
    the row above that last, blank row. You can find that row using
    Selection.End(xlDown).select
    Activecell.offset(1,0).select

    Bill

    "Raul" wrote:

    > I have a number of named ranges in a workbook, and these ranges need to be
    > updated as new data is added. Is there a way to select all the named ranges
    > in a workbook and read the contents in the "refers to:" field? As an aside,
    > could these ranges be set up to be dynamic so they will update themselves to
    > fit the data?
    >
    > Thanks in advance,
    > Raul
    >
    >


  6. #6
    Ron Coderre
    Guest

    RE: Update Named Ranges

    Try this example to get familiar with the technique:

    On a blank sheet:
    A1: a
    A2: b
    A3: c

    B1: 1
    B2: 2
    B3: 3

    Next:
    Insert>Name>Define
    -Names in workbook: type rngDynamic
    -Refers To: =OFFSET(Sheet1!$A$1:$B$1,,,COUNTA(Sheet1!$A:$A),)
    -Clidk [OK]

    To test the range name:
    Edit>Go to> Enter rngDynamic (it won't display automatically)
    Click the [OK] button to select the range

    Now, add more data immediately below cells A3 and B3

    Repeat the test....The named range now refers to the expanded data.

    Note: to use the dynamic named range in a formula....
    Press the [F3] key to see a list of all named ranges (including dynamic ones)

    Does that help?

    ••••••••••
    Regards,
    Ron


    "Raul" wrote:

    > I have a number of named ranges in a workbook, and these ranges need to be
    > updated as new data is added. Is there a way to select all the named ranges
    > in a workbook and read the contents in the "refers to:" field? As an aside,
    > could these ranges be set up to be dynamic so they will update themselves to
    > fit the data?
    >
    > Thanks in advance,
    > Raul
    >
    >


  7. #7
    Raul
    Guest

    RE: Update Named Ranges

    Many thanks to everyone for the responses; it looks like offset is the way to
    go.

    I really appreciate the examples, the code, and the explanations!

    Thanks again,
    Raul

    P.S., I am continually amazed with the quality and the timeliness of the
    responses to questions posted on this newsgroup. I cannot put into words
    what a valuable asset this is to me and all the users of Microsoft products.


    "Raul" wrote:

    > I have a number of named ranges in a workbook, and these ranges need to be
    > updated as new data is added. Is there a way to select all the named ranges
    > in a workbook and read the contents in the "refers to:" field? As an aside,
    > could these ranges be set up to be dynamic so they will update themselves to
    > fit the data?
    >
    > Thanks in advance,
    > Raul
    >
    >


Closed 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