Insert blank rows repeatedly between every data row in Excel
 

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.

Insert blank rows repeatedly between every data row in Excel
 

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


--
abcdexcel
------------------------------------------------------------------------
abcdexcel's Profile: http://www.excelforum.com/member.php...o&userid=30021
View this thread: http://www.excelforum.com/showthread...hreadid=499280

Insert blank rows repeatedly between every data row in Excel
 

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


"abcdexcel" wrote in
message ...

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


--
abcdexcel
------------------------------------------------------------------------
abcdexcel's Profile:
http://www.excelforum.com/member.php...o&userid=30021
View this thread: http://www.excelforum.com/showthread...hreadid=499280