Inserting a Row Range at regular intervals
 

Hi, I hope some of you can help me.

I have a spreadsheet that has a row of data evenly spaced down my
spreadhsheet (say, for the sake of it, each row has 10 rows in between
it and the previous one.

I need to insert a named row range, copied from another worksheet, 2
lines before each row (so that there is a blank line in between the
inserted range and the next row). (I'm thinking that this is a better
way than counting 8 rows down, inserting, then counting down the
height of the range + 2 to get to the next row).

It doesn't matter if it goes too far - I can always delete the
unneeded inserted ranges.

I presume I'd need to copy the range and use Selection.Insert
Shift:=xlDown. I'd originally thought about adding blank rows and
copying/pasting into it, but that seemed to be overkill.

How do I keep control of the activecell in order to make sure the
inserts go in the correct place?
Is there a quick way to copy the named range rather than jumping
between worksheets?

Any help you can give would be much appreciated :)
math

Inserting a Row Range at regular intervals
 

Here is a general approach:

Sub Test1()
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -10
Range("MyRange").EntireRow.Copy
Rows(i).EntireRow.Insert
Next


End Sub

you can work out the adjustments. Obviously test on copies of your data.

--
Regards,
Tom Ogilvy


" wrote:

Hi, I hope some of you can help me.

I have a spreadsheet that has a row of data evenly spaced down my
spreadhsheet (say, for the sake of it, each row has 10 rows in between
it and the previous one.

I need to insert a named row range, copied from another worksheet, 2
lines before each row (so that there is a blank line in between the
inserted range and the next row). (I'm thinking that this is a better
way than counting 8 rows down, inserting, then counting down the
height of the range + 2 to get to the next row).

It doesn't matter if it goes too far - I can always delete the
unneeded inserted ranges.

I presume I'd need to copy the range and use Selection.Insert
Shift:=xlDown. I'd originally thought about adding blank rows and
copying/pasting into it, but that seemed to be overkill.

How do I keep control of the activecell in order to make sure the
inserts go in the correct place?
Is there a quick way to copy the named range rather than jumping
between worksheets?

Any help you can give would be much appreciated :)
math