Update Named Ranges

[Guest]

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

 

[Bob Phillips]

--

HTH

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

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.