Inserting blank rows in Excell

  • Thread starter Thread starter JulieA
  • Start date Start date
J

JulieA

How do i insert two blank rows after every existing data row in excell
without doing it manually?
 
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
 
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
Click to expand...
 
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.

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
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
 
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

Similar Threads

How do I insert a row after every row of data in XL? 5
Inserting blank rows 3
Insert Blank Row with data match change 1
insert 3 blank rows after every 2 rows 1
Deleting blank rows 4
Need to highlight cells in special order 0
How can I insert a blank row after every four rows in Excel? 2
Problem in inserting rows 1

Back
Top