How do I automatically insert rows

[colwyn]

I have around 50,000 rows of data down each sheet.
These are made up of a contiuous series of approx 4000 blocks of data.
The blocks of data vary between 2 and 30 rows deep.
What I want to do is insert a blank row after each block of data.
Manually, over 6 sheets, I would have to do around 24,000 copy/pastes
and this isn't practical.
Does anyone know how it can be set up to run automatically??

Any helpful suggestions/comments would be appreciated and I'm familiar
with Excel macros - if that's at all useful.
Thanks.
Colwyn.

 

[Don Guillett]

something like this
sub insertrows()
for i=cells(rows.count,"a").end(xlup).row to 2 step -1
if cells(i-1,"a")<>cells(i,"a") then rows(i).insert
next i
end sub

 

[colwyn]

something like this
sub insertrows()
for i=cells(rows.count,"a").end(xlup).row to 2 step -1
if cells(i-1,"a")<>cells(i,"a") then rows(i).insert
next i
end sub

Don, thanks for your reply. As there is always a minimum of 2 rows
between the blocks of data your formula inserts two rows instead of
the required one row. It's nearly there but not quite. Can you fine
tune it? Or is that not possible ??
Thanks.
Colwyn.

 

[Don Guillett]

You did NOT mention existing blank rows. I always wonder why OP's can't
fully state their problem in the first place. Let me understand. You have a
block of data. Under that block there are TWO blank rows already and you
want a THIRD??? Before/after examples are always desirable.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

something like this
sub insertrows()
for i=cells(rows.count,"a").end(xlup).row to 2 step -1
if cells(i-1,"a")<>cells(i,"a") then rows(i).insert
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message

Don, thanks for your reply. As there is always a minimum of 2 rows
between the blocks of data your formula inserts two rows instead of
the required one row. It's nearly there but not quite. Can you fine
tune it? Or is that not possible ??
Thanks.
Colwyn.

 

[colwyn]

You did NOT mention existing blank rows. I always wonder why OP's can't
fully state their problem in the first place. Let me understand. You havea
block of data. Under that block there are TWO blank rows already and you
want a THIRD??? Before/after examples are always desirable.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software




Don, thanks for your reply.  As there is always a minimum of 2 rows
between the blocks of data your formula inserts two rows instead of
the required one row. It's nearly there but not quite. Can you fine
tune it? Or is that not possible ??
Thanks.
Colwyn.

No Don. I have columns of data. Column B is continuous. It lists names
in blocks of between 2 and 30 rows.

In column A each block is numbered in the top row.

All following cells in column A will be blank until the beginning of
block 2 when the number 2 will appear.

I want to insert a blank row between each block of data.

Please let me know if I'm not making it as clear as I perhaps should
be (I'm not too experienced is my only excuse!!).
Big thanks.
Colwyn.

 

[Don Guillett]

Perhaps you can just send your file to my address below along with snippets
of these msgs so I will know who I am helping.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

You did NOT mention existing blank rows. I always wonder why OP's can't
fully state their problem in the first place. Let me understand. You have
a
block of data. Under that block there are TWO blank rows already and you
want a THIRD??? Before/after examples are always desirable.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message




Don, thanks for your reply. As there is always a minimum of 2 rows
between the blocks of data your formula inserts two rows instead of
the required one row. It's nearly there but not quite. Can you fine
tune it? Or is that not possible ??
Thanks.
Colwyn.

No Don. I have columns of data. Column B is continuous. It lists names
in blocks of between 2 and 30 rows.

In column A each block is numbered in the top row.

All following cells in column A will be blank until the beginning of
block 2 when the number 2 will appear.

I want to insert a blank row between each block of data.

Please let me know if I'm not making it as clear as I perhaps should
be (I'm not too experienced is my only excuse!!).
Big thanks.
Colwyn.

 

[colwyn]

Perhaps you can just send your file to my address below along with snippets
of these msgs so I will know who I am helping.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






No Don. I have columns of data. Column B is continuous. It lists names
in blocks of between 2 and 30 rows.

In column A each block is numbered in the top row.

All following cells in column A will be blank until the beginning of
block 2 when the number 2 will appear.

I want to insert a blank row between each block of data.

Please let me know if I'm not making it as clear as I perhaps should
be (I'm not too experienced is my only excuse!!).
Big thanks.
Colwyn.

Don, thanks for your help in this matter. I've sorted it out now -
much to my surprise !!

However, (oh dear!) ...there is perhaps something else you may be
able to help with. It's related to the above.

The spreadsheet contains a series of blocks of data and there are no
breaks between rows. I want to put a blank row between each block of
data. Any suggestions as to how I might go about this ??
It would be really helpful if I could achieve this end.
Thanks.
Colwyn.

 

[Don Guillett]

See my original answer. Please answer at the TOP of messages here.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Perhaps you can just send your file to my address below along with
snippets
of these msgs so I will know who I am helping.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message






No Don. I have columns of data. Column B is continuous. It lists names
in blocks of between 2 and 30 rows.

In column A each block is numbered in the top row.

All following cells in column A will be blank until the beginning of
block 2 when the number 2 will appear.

I want to insert a blank row between each block of data.

Please let me know if I'm not making it as clear as I perhaps should
be (I'm not too experienced is my only excuse!!).
Big thanks.
Colwyn.

Don, thanks for your help in this matter. I've sorted it out now -
much to my surprise !!

However, (oh dear!) ...there is perhaps something else you may be
able to help with. It's related to the above.

The spreadsheet contains a series of blocks of data and there are no
breaks between rows. I want to put a blank row between each block of
data. Any suggestions as to how I might go about this ??
It would be really helpful if I could achieve this end.
Thanks.
Colwyn.

 

[colwyn]

Thanks Don. Please be assured that I am very grateful for any help you
give.
Now the code you initially gave me works fine when all cells in column
A have numbers in. Thing is, only the first cell of each block of data
contains the number. So where block 1 has 5 rows, cell A1 is numbered
1 and is followed by blank cells in A2:A5 - then we see number 2
appearing in A6 at the start of block 2. Is there a workaround for
this? Or do all cells in column A have to be numbered??
Thanks again.
Colwyn.

 

[Don Guillett]

I guess I am just dense. First we have no blanks>then we do>then we
don't>then we do. I repeat my offer to look at your file instead. Give
BEFORE/AFTER examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Thanks Don. Please be assured that I am very grateful for any help you
give.
Now the code you initially gave me works fine when all cells in column
A have numbers in. Thing is, only the first cell of each block of data
contains the number. So where block 1 has 5 rows, cell A1 is numbered
1 and is followed by blank cells in A2:A5 - then we see number 2
appearing in A6 at the start of block 2. Is there a workaround for
this? Or do all cells in column A have to be numbered??
Thanks again.
Colwyn.

 

[Gord Dibben]

colwyn

See my post for inserting the numbers in the blank cells.


Gord

 

[colwyn]

Thanks Don, sorry for any confusion. I've got a file to send so you
can see more clearly what the need is for but for the life of me I
simply cannot see a facility whereby I can send it!
Thanks for your patience.
Colwyn.

 

[colwyn]

Thanks for your help in this matter Don. It's now sorted and I'm
extremely grateful to you.
Thanks.
Colwyn.

 

[Don Guillett]

Reply to this email or send a separate email to my address and ATTACH the
file. On my email vista mail it looks like a paperclip...................
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Thanks Don, sorry for any confusion. I've got a file to send so you
can see more clearly what the need is for but for the life of me I
simply cannot see a facility whereby I can send it!
Thanks for your patience.
Colwyn.