Extending Named Ranges

[marston.gould]

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.

 

[Wolf]

Hi,

have a look at
http://www.contextures.com/xlNames01.html#Dynamic

 

[Norman Jones]

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

 

[Bob Phillips]

Try something like

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

 

[marston.gould]

Thank Bob, Norman - both work perfectly.