MrExcel Publishing
Your One Stop for Excel Tips & Solutions

insert row every fifth space


Posted by Andonny on December 09, 2000 4:51 PM

Hi,
I would like to insert an empty row every fifth space to the end of my spreadsheet automatically and moving the existing data accordingly.

Thank you very much for your kind help
Andonny


Posted by Celia on December 10, 2000 4:49 PM

Andonny
Select a cell (or cells, or an entire row) where you want the first row inserted and then run this macro :-

Sub Insert_Row()
Dim insertRow As Range, rws%, LastRow&
rws = 5 'Number of rows between each inserted row
Set insertRow = Selection.EntireRow
Application.ScreenUpdating = False
Do
insertRow.Insert
Set insertRow = insertRow.Offset(rws, 0)
With ActiveSheet
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
End With
Loop While insertRow.Row <= LastRow
End Sub

Celia