Inserting blank rows between each line

[Tmayeux]

Happy Friday Everyone.

I'm in 2007 and need to insert a row between every other line in a 30,000+ row document.

To illustrate what I need:

A1 full of data
A2 want blank row
A3 full of data
A4 want blank row

This is what I have:
A1 data
A2 data
A3 data
A4 data

I appreciate any insight. Thanks again.

 

[VoG]

Hello and welcome to MrExcel.

Click the Office button (top left), click Excel Options then tick Show Developer Tab in the Ribbon and click OK.

Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste in

Sub test()
Dim i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    Rows(i).Insert
Next i
Application.ScreenUpdating = True
End Sub

Press ALT + Q to return to your sheet, on the Developer tab click Macros, click on test then click the Run button.

 

[hiker95]

Tmayeux,

Try:

For Excel 2007: Click on the "Office Button" (top left), click "Excel Options", "Top Options for working with Excel", then tick "Show Developer tab in the Ribbon". Then click on the "OK" button.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Press the keys ALT+Q to exit the Editor, and return to Excel.

Option Explicit
Sub InsertRows()
Dim LR As Long, a As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = LR To 2 Step -1
  Cells(a, 1).EntireRow.Insert
Next a
End Sub

Then run the "InsertRows" macro.