Insert blank rows repeatedly between every data row in Excel

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

 

[abcdexcel]

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

 

[JMay]

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