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.

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

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

On Sep 9, 5:10*pm, "Don Guillett" wrote:
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
"colwyn" wrote in message

...

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, 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.

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

"colwyn" wrote in message
...
On Sep 9, 5:10 pm, "Don Guillett" wrote:
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
"colwyn" wrote in
message

...

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, 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.

On Sep 10, 1:09*pm, "Don Guillett" wrote:
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
"colwyn" wrote in message

...
On Sep 9, 5:10 pm, "Don Guillett" wrote:



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

....

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, 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.

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

"colwyn" wrote in message
...
On Sep 10, 1:09 pm, "Don Guillett" wrote:
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
"colwyn" wrote in
message

...
On Sep 9, 5:10 pm, "Don Guillett" wrote:



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

...

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, 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.

On Sep 10, 1:43*pm, "Don Guillett" wrote:
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
"colwyn" wrote in message

...
On Sep 10, 1:09 pm, "Don Guillett" wrote:



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

....
On Sep 9, 5:10 pm, "Don Guillett" wrote:

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

....

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, 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, 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.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"colwyn" wrote in message
...
On Sep 10, 1:43 pm, "Don Guillett" wrote:
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
"colwyn" wrote in
message

...
On Sep 10, 1:09 pm, "Don Guillett" wrote:



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

...
On Sep 9, 5:10 pm, "Don Guillett" wrote:

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

...

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, 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, 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.

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.




On Sep 10, 11:19*pm, "Don Guillett" wrote:
See my original answer. Please answer at the TOP of messages here.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"colwyn" wrote in message

...
On Sep 10, 1:43 pm, "Don Guillett" wrote:



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

...
On Sep 10, 1:09 pm, "Don Guillett" wrote:

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

....
On Sep 9, 5:10 pm, "Don Guillett" wrote:

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

...

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, 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, 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.

I guess I am just dense. First we have no blanksthen we dothen we
don'tthen we do. I repeat my offer to look at your file instead. Give
BEFORE/AFTER examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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




On Sep 10, 11:19 pm, "Don Guillett" wrote:
See my original answer. Please answer at the TOP of messages here.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"colwyn" wrote in
message

...
On Sep 10, 1:43 pm, "Don Guillett" wrote:



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

...
On Sep 10, 1:09 pm, "Don Guillett" wrote:

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

...
On Sep 9, 5:10 pm, "Don Guillett" wrote:

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

...

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, 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, 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

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


Gord

On Thu, 11 Sep 2008 01:11:02 -0700 (PDT), colwyn
wrote:

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.




On Sep 10, 11:19*pm, "Don Guillett" wrote:
See my original answer. Please answer at the TOP of messages here.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"colwyn" wrote in message

...
On Sep 10, 1:43 pm, "Don Guillett" wrote:



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

...
On Sep 10, 1:09 pm, "Don Guillett" wrote:

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

...
On Sep 9, 5:10 pm, "Don Guillett" wrote:

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

...

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, 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, 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.

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.




On Sep 11, 1:23*pm, "Don Guillett" wrote:
I guess I am just dense. First we have no blanksthen we dothen we
don'tthen we do. I repeat my offer to look at your file instead. Give
BEFORE/AFTER examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"colwyn" wrote in message

...
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.

On Sep 10, 11:19 pm, "Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"colwyn" wrote in
message

....
On Sep 10, 1:43 pm, "Don Guillett" wrote:

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

....
On Sep 10, 1:09 pm, "Don Guillett" wrote:

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

...
On Sep 9, 5:10 pm, "Don Guillett" wrote:

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

...

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, 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, 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.

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


On Sep 11, 4:18*pm, colwyn wrote:
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.

On Sep 11, 1:23*pm, "Don Guillett" wrote:

I guess I am just dense. First we have no blanksthen we dothen we
don'tthen we do. I repeat my offer to look at your file instead. Give
BEFORE/AFTER examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"colwyn" wrote in message

....
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.

On Sep 10, 11:19 pm, "Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"colwyn" wrote in
message

....
On Sep 10, 1:43 pm, "Don Guillett" wrote:

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

...
On Sep 10, 1:09 pm, "Don Guillett" wrote:

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

...
On Sep 9, 5:10 pm, "Don Guillett" wrote:

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

...

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, 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, 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.

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

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




On Sep 11, 1:23 pm, "Don Guillett" wrote:
I guess I am just dense. First we have no blanksthen we dothen we
don'tthen we do. I repeat my offer to look at your file instead. Give
BEFORE/AFTER examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"colwyn" wrote in
message

...
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.

On Sep 10, 11:19 pm, "Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"colwyn" wrote in
message

...
On Sep 10, 1:43 pm, "Don Guillett" wrote:

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

...
On Sep 10, 1:09 pm, "Don Guillett" wrote:

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

...
On Sep 9, 5:10 pm, "Don Guillett" wrote:

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

...

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, 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, 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.