Insert Row directly below a named range.

[Baziwan]

Hi guys and girls,

So here's the problem.
Lets say that A1:E10 is a named range(namedrange1) and F1:J10 is a named range(namedrange2).
How do i insert a row between the two named ranges. Specifically I need to know how to do it referencing (namedrange1)

I know how to do it referencing (namedrange2)

Set rCl = Range(namedrange2).Rows(1)
               rCl.EntireRow.Insert shift:=xlDown

This code inserts a row above (namedrange2). How do I do the same thing but referencing (namedrange1) so that it inserts the row below (namedrange1). I can't use

.End

because the cells in (namedrange2) all have a value and so it just inserts the row below the last used cell. Please also bear in mind that the number of rows in a named range is dynamic it could be 5 or 105 so offsetting from the first row of the named range isn't an option.

If anyone can help it'll be appreciated.

Thanks in advance

[ragulduy]

Maybe:

Sub macro_1()
Dim a, max_row
For Each a In Range("NamedRange2").Cells
    If a.Row > max_row Then max_row = a.Row
Next
Rows(a + 1).Insert shift:=xlDown
End Sub

[HaHoBe]

Hi, Baziwan,

Set rCl = Range("namedrange2").Cells(Range("namedrange2").Cells.Count).Offset(1)
rCl.EntireRow.Insert shift:=xlDown

Ciao,
Holger

[isskint]

I'm a little confused. Both ranges occupy the same rows, so how can you insert a row between them?
If namedrange1 is A1:E10 and namedrange2 was F11:J20, then you could insert on the row at the start of namedrange2.

Do you mean insert Column?

Either way use for rows;

Dim intEndRow As Integer

intEndRow = Range("namedrange1").Rows.Count + 1

Rows(intEndRow).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

and this for columns;

Dim intEndColumn As Integer

intEndColumn = Range("namedrange1").Columns.Count + 1

Columns(intEndColumn).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove