|
How do I automatically insert rows
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. |
How do I automatically insert rows
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. |
How do I automatically insert rows
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. |
How do I automatically insert rows
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. |
How do I automatically insert rows
|
How do I automatically insert rows
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. |
How do I automatically insert rows
|
How do I automatically insert rows
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. |
How do I automatically insert rows
|
How do I automatically insert rows
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. |
How do I automatically insert rows
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. |
How do I automatically insert rows
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. |
How do I automatically insert rows
|
| All times are GMT +1. The time now is 10:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com