Inserting multiple, non-adjacent rows

[Guest]

Hi:

I have a spreadsheet with, say, 500 rows of data. I want to insert a blank
row between each of the 500. Is there a macro I can use to do this? It's
something I'll be doing often, and for reasons too complex for here, I cannot
add the blank rows until **after** all the data has been put into the
spreadsheet.

Any help appreciated - and I have a deadline for this &&%$#!! sheet, so any
QUICK is especially appreciated!!

Mick

 

[Max]

One way to try ..

Assuming source data is in Sheet1, cols A to C, from row1 down

In a new Sheet2,

Put in say, A1:
=IF(MOD(ROW(A1),2)=0,"",
OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/2),COLUMN(A1)-1))

Copy A1 across to C1, fill down until zeros appear
signalling exhaustion of data from Sheet1

(Need to fill down by at least double the number of rows
as the source data in Sheet1)

Sheet2 returns the data rows from Sheet1,
interspersed with alternating blank rows

If required, select cols A to C and kill all formulas with an "in-place" :
Copy > Paste special > check "Values" > OK

 

[Norman Jones]

Hi Mick,

Try:

'=============>>
Public Sub Tester()
Dim LRow As Long
Dim i As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

LRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = LRow To 2 Step -1
Rows(i).Insert
Next i

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<=============