Posted to microsoft.public.excel.programming
|
|
Extending Named Ranges
Hi,
have a look at
http://www.contextures.com/xlNames01.html#Dynamic
-----Original Message-----
I have 3 independent named ranges (e.g. they do not
share any of the
same cells) and do not have any cells between them that
are on the same
row - example:
range1 is from J9 to U15
range2 if from J20 to U23
range3 is from j25 to U50
the exact number of rows contained in each of these
ranges will vary
from time to time.
I've added a custom menu that has three choices related
to each range.
If the corresponding item is selected, I'd like to take
the related
range,
insert a row below it, extend the named range to that
row, and copy any
formating or formulas
to the added row.
For instance, if the .On Action for the first item were
chosen,
I'd like it to insert a row below row 15, extend range1
from J9 to U16
and copy the formating and formulas from j15 through u15
to j16 through
u16.
If its selected again, it would add a row below 16,
extend range1 from
j9 to u17, etc.
I've found a way to work with the cells when I know their
location/region, but not when I don't know the rows they
inhabit - e.g.
making it more general.
.
|