| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I automatically insert rows
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatically insert rows with formula | Excel Worksheet Functions | |||
| Automatically Insert New Rows | Excel Discussion (Misc queries) | |||
| How can I set excel to automatically insert rows | Excel Worksheet Functions | |||
| Automatically insert/delete rows to be x apart | Excel Discussion (Misc queries) | |||
| how do you have rows automatically insert in excel? | Excel Worksheet Functions | |||
Hybrid Mode
