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.

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.

.

Hi Marston,

One way:

Sub Tester()
Dim rng As Range

Set rng = Range("MyRangeName")

With rng.Rows
rng.Rows(.Count).Offset(1).EntireRow.Insert
rng.Rows(.Count).Copy rng.Rows(.Count).Offset(1)
rng.Resize(.Count + 1).Name = "MyRangeName"
End With

End Sub

---
Regards,
Norman



" wrote in message
oups.com...
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.

Try something like

With Range("bob")
.Cells(.Rows.Count + 1, .Columns.Count).EntireRow.Insert
.Resize(.Rows.Count + 1, .Columns.Count).Name = "bob"
End With


--

HTH

RP

" wrote in message
oups.com...
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.

Thank Bob, Norman - both work perfectly.