Inserting blank rows in Excell

[JulieA]

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

 

[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

 

[JulieA]

Where would I insert this macro?

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

 

[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.

~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[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.

~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[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

 

[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