Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Multiple Rows
Is there a way to insert multiple rows at a single time? Let me see if I can
explain. I have an existing worksheet contain several hundreds rows on data. I want to insert 16 new rows between each existing row of data. I can do this by going to each row and inserting the rows but I was hoping there would be a way to this in one fell swoop. Any thoughts. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Multiple Rows
Copy 16 empty rows.
Right-click on a cell and choose Insert copied cells. -- Kind regards, Niek Otten "Mike" wrote in message ... Is there a way to insert multiple rows at a single time? Let me see if I can explain. I have an existing worksheet contain several hundreds rows on data. I want to insert 16 new rows between each existing row of data. I can do this by going to each row and inserting the rows but I was hoping there would be a way to this in one fell swoop. Any thoughts. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Multiple Rows
If you highlight 16 rows first the Insert Row command will insert 16 above
your selected rows. HTH Sheila "Mike" wrote: Is there a way to insert multiple rows at a single time? Let me see if I can explain. I have an existing worksheet contain several hundreds rows on data. I want to insert 16 new rows between each existing row of data. I can do this by going to each row and inserting the rows but I was hoping there would be a way to this in one fell swoop. Any thoughts. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Multiple Rows
If I understand, you want 16 new blank rows between each of the existing rows, correct? Assume you have 500 rows of data with a title row at the top. Your data is in rows 2-501. 1) To do this, insert new columns A & B. Fill col A with an index number for each row (1-500). Fill col B with 1's for all 500 rows of data. 2) Copy Cols A & B directly below the existing data (in this example, into cols A & B of rows 502-1001.) Replace the 1's in Col B of the new rows with 2's. 3) Repeat step 2 except replacing the 1's with 3's, etc, until you reach 17. You now have 500 rows of data with 1 thru 500 in col A and 1 in col B, followed by 500 otherwise blanks rows with 1 thru 500 in col A and 2's in col B, etc, for a total of 9000 rows. 4) Sort the data with the first sort on col A and the second on col B. This will sort the blank rows numbered 2-17 (i.e., 16 blank rows) up into the data. You can now delete Col A & B. If you have to do this often, or to make this a bit easier, in step 2 you can enter 0 in cell B1, then put the formula =B1+1 in cell B2 (next to the index 1 in A2) and =B2 in cell B3, then copy B3 down to the end of the data. When you copy cells A2:B501 for step 3, the numbering for col B will be done automatically. It's a good idea to copy cols A & B and use Paste/Special/Values to replace the formulae with values before you do the sort, in case you need to re-sort it later. Hope this gets you what you need. ---Glenn -- gjcase ------------------------------------------------------------------------ gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=510102 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Multiple Rows
In a standard Module enter:
'Below assumes You have data in all rows of Column A 'and Row 1 contains a header sescription - data beginning on row 2.. Sub Foo() Irow = Range("A65536").End(xlUp).Row Do Until Irow = 2 Set Rng = Range("A" & Irow) Rng.Resize(16, 1).EntireRow.Insert Irow = Irow - 1 Loop End Sub "Mike" wrote: Is there a way to insert multiple rows at a single time? Let me see if I can explain. I have an existing worksheet contain several hundreds rows on data. I want to insert 16 new rows between each existing row of data. I can do this by going to each row and inserting the rows but I was hoping there would be a way to this in one fell swoop. Any thoughts. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can i wrap rows to form multiple rows per row to fit on 1 sheet? | Excel Discussion (Misc queries) | |||
Inserting rows in multiple workbooks | Excel Discussion (Misc queries) | |||
Sort with multiple header rows | Excel Discussion (Misc queries) | |||
Multiple rows converted to one row | Excel Worksheet Functions | |||
Convert multiple columns to rows | Excel Worksheet Functions |