Inserting Multiple Rows

[Mike]

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.

[Niek Otten]

Copy 16 empty rows.
Right-click on a cell and choose Insert copied cells.

--
Kind regards,

Niek Otten


"Mike" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Sheila D]

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.

[gjcase]

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

[Jim May]

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.