Inserting Rows Through a Macro

M

Mike

I'm trying to create a macro in Excel 2002 where, I can
insert two rows between every number in column A:

Column A
1
2
3
4
5
6
7
8
9

Any ideas? Thanks in advance.
 
C

Cecilkumara Fernando

Mike,
Try

Sub insertrows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
Range("A" & i & ":A" & (i + 1)).EntireRow.Insert
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Cecil
 
M

Mike

Cecil,
Thanks so much! That worked like a champ. Wondering if
you can help me with the second piece, where I'm trying
to paste formulas into the blank rows i've just
inserted. Again, this would be repeated down the entire
spreadsheet. I've named a range of cells w/formulas that
I'd like to copy "formularange".

Thanks again.
 
C

Cecilkumara Fernando

Mike,
you can try it by adding this line just before Next i
Range("A" & i & ":A" & (i + 1)).Formula = _
Range("formularange").Formula
or this line
Range("formularange").Copy _
Destination:=Range("B" & i)
But I think you need a different approach.
Cecil
 
Top