software

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.

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

--
Kind regards,

Niek Ottenquot;Mikequot; gt; wrote in message
...
gt; Is there a way to insert multiple rows at a single time? Let me see if I
gt; can
gt; explain.
gt; I have an existing worksheet contain several hundreds rows on data. I want
gt; to insert 16 new rows between each existing row of data. I can do this by
gt; going to each row and inserting the rows but I was hoping there would be a
gt; way to this in one fell swoop.
gt;
gt; Any thoughts.
If you highlight 16 rows first the Insert Row command will insert 16 above
your selected rows.
HTH Sheila

quot;Mikequot; wrote:

gt; Is there a way to insert multiple rows at a single time? Let me see if I can
gt; explain.
gt; I have an existing worksheet contain several hundreds rows on data. I want
gt; to insert 16 new rows between each existing row of data. I can do this by
gt; going to each row and inserting the rows but I was hoping there would be a
gt; way to this in one fell swoop.
gt;
gt; Any thoughts.


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 amp; 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 amp; B directly below the existing data (in this example,
into cols A amp; 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 amp; 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 amp; 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: www.excelforum.com/member.php...oamp;userid=26061
View this thread: www.excelforum.com/showthread...hreadid=510102In 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(quot;A65536quot;).End(xlUp).Row
Do Until Irow = 2
Set Rng = Range(quot;Aquot; amp; Irow)
Rng.Resize(16, 1).EntireRow.Insert
Irow = Irow - 1
Loop
End Sub

quot;Mikequot; wrote:

gt; Is there a way to insert multiple rows at a single time? Let me see if I can
gt; explain.
gt; I have an existing worksheet contain several hundreds rows on data. I want
gt; to insert 16 new rows between each existing row of data. I can do this by
gt; going to each row and inserting the rows but I was hoping there would be a
gt; way to this in one fell swoop.
gt;
gt; Any thoughts.