Inserting Rows

  • Thread starter Thread starter Phil
  • Start date Start date
P

Phil

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?
 
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.
 
-----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
Click to expand...
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.
 
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
 
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 **
 
Thanks!

Regards,
Tom Ogilvy

Paul B said:
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 **
Click to expand...
 
Back
Top