Inserting blank rows in Excell

J

JulieA

How do i insert two blank rows after every existing data row in excell
without doing it manually?
 
S

Sheeloo

Use a macro like the one below
Sub InsertRows()
Dim i As Long, nRows As Integer, nEvery As Integer
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
nRows = 2 'number of rows to insert
For i = lastrow To 1 Step -1
If Not IsEmpty(Cells(i, 1)) Then
Rows(i + 1 & ":" & i + nRows).Insert
End If
Next
End Sub
 
J

JulieA

Where would I insert this macro?

Sheeloo said:
Use a macro like the one below
Sub InsertRows()
Dim i As Long, nRows As Integer, nEvery As Integer
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
nRows = 2 'number of rows to insert
For i = lastrow To 1 Step -1
If Not IsEmpty(Cells(i, 1)) Then
Rows(i + 1 & ":" & i + nRows).Insert
End If
Next
End Sub
 
F

Flintstone

Sheeloo's macros worked perfectly for me using Excel 2007.
Assuming that you are running Excel 2007, follow the steps below.

On the Developer tab
Click Visual Basic
Insert
Module
Copy Text Below beginning with Sub InsertRows and ending with End Sub.

Sub InsertRows()
Dim i As Long, nRows As Integer, nEvery As Integer
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
nRows = 2 'number of rows to insert
For i = lastrow To 1 Step -1
If Not IsEmpty(Cells(i, 1)) Then
Rows(i + 1 & ":" & i + nRows).Insert
End If
Next
End Sub

File
Close and return to Excel

Still on the Developer tab
Click Macro
Highlight InsertRows
Run

Hope this helps.

~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
D

Don Guillett

or. I think I answered this about a week ago.

Sub inserttwoblankrows()
Dim i As Long
Dim mc As String
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
Rows(i).Resize(2).Insert
Next i
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top