Insert line every x lines until end of data

[Guest]

Hi,

I am trying to write a macro that will insert a new line after every 5 lines
of data and keep doing this until there are no more lines of data (1,000+
lines).

If anyone could help I would really appreciate it.

 

[Gary Keramidas]

change the column in the lastrow statement to your longest column of data and
the sheet name in the set statement

this may get you started

Sub insertLines()
Dim i As Long
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow Step 5
Rows(i).EntireRow.Insert
Next

End Sub

 

[Gary Keramidas]

another way:

Option Explicit

Sub insertLines()
Dim i As Long
Dim ws As Worksheet
Dim lastrow As Long
Application.ScreenUpdating = False
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow

If i Mod 5 = 0 Then
Rows(i).EntireRow.Insert
End If
Next
Application.ScreenUpdating = True

End Sub

 

[Guest]

I don't think that either of Gary's macros will work. He has not taken into
account that the number of rows increase as the rows are inserted.

Here is a simplistic one:-

Sheets("Sheet1").Select
Range("A1").Select 'Set range to start point
Do While ActiveCell.Value <> ""
ActiveCell.Offset(5, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
Loop

Regards,

OssieMac

 

[Gary Keramidas]

yes it was late, i should have at least started at the lastrow.

your suggestion is fine.