Hi! I know there is a way to do this but I cannot remember how to do it....

I know I did it in the past and can't for the life of me remeber how I
accomplished it.

I'm using Excel 2003. I have a long column of numbers. I want to insert 4
rows after each number and then copy that number into each new row.

Any thoughts how to accomplish this automatically?

Denise

Try something like

Dim LastRow As Long
Dim Ndx As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For Ndx = LastRow To 1 Step -1
Rows(Ndx).Resize(4).Insert
Rows(Ndx + 4).Resize(5, 1).Value = Rows(Ndx + 4).Cells(1,
"A").Value
Next Ndx



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Denise" wrote in message
news:1132596976.351b253b8f44b61a06e46a3c9d4ea5e7@n ews.meganetnews.com...
Hi! I know there is a way to do this but I cannot remember how
to do it....

I know I did it in the past and can't for the life of me
remeber how I
accomplished it.

I'm using Excel 2003. I have a long column of numbers. I want
to insert 4
rows after each number and then copy that number into each new
row.

Any thoughts how to accomplish this automatically?

Denise

Sub Add4rows()
Dim lastrow as Long
Dim i as Long
lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 1 Step -1
cells(i+1).Resize(4,1).EntireRow.Insert
cells(i+1).Resize(4,1).Value = cells(i,1).Value
Next
End Sub

Code is untested an may contain typos which you can readily correct.

--
Regards,
Tom Ogilvy


"Denise" wrote in message
news:1132596976.351b253b8f44b61a06e46a3c9d4ea5e7@n ews.meganetnews.com...
Hi! I know there is a way to do this but I cannot remember how to do
it....

I know I did it in the past and can't for the life of me remeber how I
accomplished it.

I'm using Excel 2003. I have a long column of numbers. I want to insert
4
rows after each number and then copy that number into each new row.

Any thoughts how to accomplish this automatically?

Denise