Automatically Add Rows to Excel File

ekeenan81

New Member
Joined
Dec 8, 2010
Messages
39
I have an excel file that is about 7,000 lines.

I want to have a macro that auto creates two blank rows in between each of these lines.

The best VBA code I have come up with only puts 2 rows at the top of the file which is not helpful.

Any help is appreciated

Ex.

A B C D E F G


X Y Z A B C D

Sub Insert_Rows_Loop()
Dim CurrentSheet As Object

' Loop through all selected sheets.
For Each CurrentSheet In ActiveWindow.SelectedSheets
' Insert 2 rows at top of each sheet.
CurrentSheet.Range("a1:a2").EntireRow.Insert
Next CurrentSheet
End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
Sub Insert_Rows_Loop()
Dim i as Long, j as Long
For i = 1 to Worksheets.Count
     Sheets(i).Select  
     j = 1
     Do While Not IsEmpty(Range("A" & j))
        Range("A" & i).EntireRow.Resize(2).Insert
        j = j + 3
    Loop
Next i
End Sub
 

ekeenan81

New Member
Joined
Dec 8, 2010
Messages
39
Jack,

Thanks for the below code.

It is adding rows after the first row, but it is adding not 2, but a lot... over 500 when leaving the cursor in field A1, but the rest of the rows still have no blank rows in between.
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
try

Code:
Sub Insert_Rows_Loop2()
    Dim ws, i
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            lr = .Cells(Rows.Count, "A").End(xlUp).Row
            For i = lr To 1 Step -1
               .Range("A" & i).EntireRow.Resize(2).Insert
            Next i
        End With
    Next ws
End Sub
 

ekeenan81

New Member
Joined
Dec 8, 2010
Messages
39
Weaver,

This worked great.

Thank you as well as Jack for the pointers here.

Much appreciated.
 

Forum statistics

Threads
1,141,019
Messages
5,703,760
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top