Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a Value into a Range | Excel Programming | |||
How to trigger a macro at regular intervals? | Excel Programming | |||
Count cells in range that restarts at intervals | Excel Discussion (Misc queries) | |||
Count cells in range that restarts at intervals | Excel Discussion (Misc queries) | |||
copy/inserting rows at specific intervals | Excel Programming |