Insert blank rows repeatedly between every data row in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Could you guide me please....

I need to insert 5 blank rows repeatedly between every existing data rows
for approximately 300 rows.

If I go about doing the repeat short-cut "Control+Y", it just repeats
inserting ONE row only between the consecutive data row.

Is there some command, which helps me highlight all the rows & allows me to
insert 5 blank rows between every consecutive existing data row?

Thanks in advance for your kind advice.
 
Hi

Being very new to excel coding, this code might be lengthy, but it
works.

Public Sub insert_row()
Const TestColumn As Long = 1 'have taken column A as 1, thus B would be
2'
Dim cRows As Long
Dim i As Long

cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row
For i = cRows To 2 Step -1
If Cells(i, TestColumn).Value <> IsNotBlank Then
Cells(i, TestColumn).EntireRow.Insert
Cells(i, TestColumn).EntireRow.Insert
Cells(i, TestColumn).EntireRow.Insert
Cells(i, TestColumn).EntireRow.Insert
Cells(i, TestColumn).EntireRow.Insert
End If
Next i

End Sub


Hope this suffice your cause.

SAS
 
The 7 lines of code you have between the For Line and the Next line can be
replaced with the following 3 lines of code:

Cells(i, TestColumn).Select
ActiveCell.Rows("1:4").EntireRow.Insert
ActiveCell.Offset(-1, 0).Select

''' Just one alternative,,,
FWIW...
 
Back
Top