Insert line every x lines until end of data
 

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.

Insert line every x lines until end of data
 

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


"tiger" wrote in message
...
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.

Insert line every x lines until end of data
 

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

--


Gary


"tiger" wrote in message
...
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.

Insert line every x lines until end of data
 

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" wrote:

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

--


Gary


"tiger" wrote in message
...
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.

Insert line every x lines until end of data
 

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

your suggestion is fine.
--


Gary


"OssieMac" wrote in message
...
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" wrote:

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

--


Gary


"tiger" wrote in message
...
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.