Duplicating a row and numbering?

**nemmeh** · 03-03-2009, 10:29 AM

Hi!

I've been dealing with this headache for over 2 hours now, and I was hoping maybe some of you can help me out.

I've been given an excel file with 75 addresses (1 address entry per row) and I have to make 150 copies of each address while also numbering column D for each row 1-150.

So in the end it would go from: (sorry for the periods.. extra spacing didn't work!)
A........B................C.......D
AAA...123 Street...City...<blank>
BBB...456 Street...City...<blank>
CCC...789 Street...City...<blank>

To:
A........B................C.......D
AAA...123 Street...City...1
AAA...123 Street...City...2
AAA...123 Street...City...3~
AAA...123 Street...City...150
BBB...456 Street...City...1
BBB...456 Street...City...2
BBB...456 Street...City...3~
BBB...456 Street...City...150
CCC...789 Street...City...1
CCC...789 Street...City...2
CCC...789 Street...City...3~
CCC...789 Street...City...150

I don't mean to be lazy and just ask for a macro code, but I'm a complete excel novice and just looking for a quick and easy fix rather than copy/pasting these entries manually.. edit: this file has a deadline for it, which is the reason for the quick fix not to just get out of learning how to do it

I've tried to make a macro consisting of inserting a row, copying a row then pasting it, but that only worked for the first row that I'm duplicating.

Hopefully I made a little bit of sense of what I'm trying to do/achieve..

Thanks ahead of time!

**StephenR** · 03-03-2009, 11:43 AM

Try this, which will put the results on sheet2:

Code:

Sub x()

Dim rng As Range, n As Long

Sheet1.Activate
n = 1
For Each rng In Range("A1", Range("A1").End(xlDown))
    With Sheet2.Cells(n, 1)
        .Resize(150, 3).Value = rng.Resize(, 3).Value
        .Offset(, 3).Value = 1
        .Offset(, 3).AutoFill .Offset(, 3).Resize(150), Type:=xlFillSeries
    End With
    n = n + 150
Next rng

End Sub