Inserting Rows
 

My worksheet has the following headings:

RegNo FirstName LastName Country Registered


The sheet has over 500 rows of data. Can anyone suggest a
macro that would allow me to insert a blank row after
every 4 lines of data?

Inserting Rows
 

Sub Tester10()
Set rng = Cells(5, 1)
For i = 9 To 1000 Step 4
If IsEmpty(Cells(i, 1)) Then Exit For
Set rng = Union(rng, Cells(i, 1))
Next
If Not rng Is Nothing Then
rng.EntireRow.Insert
End If
End Sub

Tested in Excel 2000. Not sure it would work in xl97, but haven't tested
there. Should work in xl2000 and later I would think.

--
Regards,
Tom Ogilvy


"Phil" wrote in message
...
My worksheet has the following headings:

RegNo FirstName LastName Country Registered


The sheet has over 500 rows of data. Can anyone suggest a
macro that would allow me to insert a blank row after
every 4 lines of data?

Inserting Rows
 

-----Original Message-----
My worksheet has the following headings:

RegNo FirstName LastName Country Registered


The sheet has over 500 rows of data. Can anyone suggest a
macro that would allow me to insert a blank row after
every 4 lines of data?


.
c=1
do until isempty(range("a" & c)) = true
c=c+1
loop

for y=1 to c step 4
columns(y).select
seltion.insert shift:=xldown
next y

have a play around with that, you may need to alter it
slightly.

Inserting Rows
 

Phil,

This code is dependent on column A having something in all cells.
It will loop until it finds a blank cell.
It places the first insert after the 5th row (includes header) and than
separates groups into 4 rows each. Does over 500 rows almost
instantaneously in Excel97.

Application.ScreenUpdating = False ' prevents flicker, speeds it up
Dim x As Long
x = 6
Do Until Cells(x, 1) = ""
Rows(x).Insert
x = x + 5
Loop
Application.ScreenUpdating = True
--
sb
"Phil" wrote in message
...
My worksheet has the following headings:

RegNo FirstName LastName Country Registered


The sheet has over 500 rows of data. Can anyone suggest a
macro that would allow me to insert a blank row after
every 4 lines of data?

Inserting Rows
 

Tom, tested in '97 works fine

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

"Tom Ogilvy" wrote in message
...
Sub Tester10()
Set rng = Cells(5, 1)
For i = 9 To 1000 Step 4
If IsEmpty(Cells(i, 1)) Then Exit For
Set rng = Union(rng, Cells(i, 1))
Next
If Not rng Is Nothing Then
rng.EntireRow.Insert
End If
End Sub

Tested in Excel 2000. Not sure it would work in xl97, but haven't tested
there. Should work in xl2000 and later I would think.

--
Regards,
Tom Ogilvy


"Phil" wrote in message
...
My worksheet has the following headings:

RegNo FirstName LastName Country Registered


The sheet has over 500 rows of data. Can anyone suggest a
macro that would allow me to insert a blank row after
every 4 lines of data?

Inserting Rows
 

Thanks!

Regards,
Tom Ogilvy

"Paul B" wrote in message
...
Tom, tested in '97 works fine

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

"Tom Ogilvy" wrote in message
...
Sub Tester10()
Set rng = Cells(5, 1)
For i = 9 To 1000 Step 4
If IsEmpty(Cells(i, 1)) Then Exit For
Set rng = Union(rng, Cells(i, 1))
Next
If Not rng Is Nothing Then
rng.EntireRow.Insert
End If
End Sub

Tested in Excel 2000. Not sure it would work in xl97, but haven't
tested
there. Should work in xl2000 and later I would think.

--
Regards,
Tom Ogilvy


"Phil" wrote in message
...
My worksheet has the following headings:

RegNo FirstName LastName Country Registered


The sheet has over 500 rows of data. Can anyone suggest a
macro that would allow me to insert a blank row after
every 4 lines of data?