duplicate rows based on cell value

Hey folks,
I have a dataset that consists of roughly 4000 observations and I need to expand it so that I duplicate rows based on the value noted in the final column.

Here is a sample of what my data looks like:

Community Zone Population size Date Event Count
B SLZ 446 03/27/2006 ILI 5
B SLZ 446 04/03/2006 ILI 4

And I'd like for it to look like this:

Community Zone Population size Date Event Count
B S 400 03/27/2006 ILI 5
B S 400 03/27/2006 ILI 5
B S 400 03/27/2006 ILI 5
B S 400 03/27/2006 ILI 5
B S 400 03/27/2006 ILI 5
B S 400 04/03/2006 ILI 4
B S 400 04/03/2006 ILI 4
B S 400 04/03/2006 ILI 4
B S 400 04/03/2006 ILI 4

Does anyone know how I can do this using Excel 2003?

Thanks!

Thanks so much for the reply!
I'm getting an "application defined/object defined error".

The following line is highlighted:
Range("A" & Rows.Count).End(xlUp)(2).Resize(v(i, 6)).Value = v(i, 1)

Just a thought but could this be because I have entries where the count is Zero?

If this is the case, is there any way of keeping/skipping these rows and moving on to cases where the count is not zero?

Yes but what do you want to happen to those entries?

I'd like to have them still visible within the dataset. I don't want them to be deleted just yet.

Thanks!

OK, try this:

Sub x()

Dim v, i As Long, j As Long, n As Long

Application.ScreenUpdating = False

v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value

ActiveSheet.UsedRange.Offset(1).ClearContents

For i = LBound(v, 1) To UBound(v, 1)
    n = IIf(v(i, 6) = 0, 1, v(i, 6))
    With Range("A" & Rows.Count).End(xlUp)(2)
        .Resize(n).Value = v(i, 1)
        .Offset(, 1).Resize(n).Value = v(i, 2)
        .Offset(, 2).Resize(n).Value = v(i, 3)
        .Offset(, 3).Resize(n).Value = v(i, 4)
        .Offset(, 4).Resize(n).Value = v(i, 5)
        .Offset(, 5).Resize(n).Value = v(i, 6)
    End With
Next i

Application.ScreenUpdating = True

End Sub

It gives an error at the following line:
n = IIf(v(i, 6) = 0, 1, v(i, 6))

"Type mismatch error"

Can you post a workbook please, and you should use code tags.

Hey, Here it is.
Thanks!

Funny, I just ran my code on your file and it worked. Did you try on a different file?

EDIT: the error suggests a text entry in column F.

ya, it was a different file (I sent a smaller version). I also ran the code on this file and if you look at the output, instead of generating 5 entries for the first date, it generated a bunch more.
Did it work properly for you?

I noted a missing entry in the count, and deleted it. Other than this there aren't any non-numerical entries that I could see within the F column. I reran the code again and got a different error:

Run-time error '1004':
Application-defined or object-defined error

When I debug the following line is highlighted in yellow:
Range("A" & Rows.Count).End(xlUp)(2).Resize(v(i, 6)).Value = v(i, 1)

When I run this code it goes from 208 rows to 944.

Sub x()

Dim v, i As Long, j As Long, n As Long

Application.ScreenUpdating = False

v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value

ActiveSheet.UsedRange.Offset(1).ClearContents

For i = LBound(v, 1) To UBound(v, 1)
    If v(i, 6) = 0 Or Not IsNumeric(v(i, 6)) Then
        n = 1
    Else
        n = v(i, 6)
    End If
    With Range("A" & Rows.Count).End(xlUp)(2)
        .Resize(n).Value = v(i, 1)
        .Offset(, 1).Resize(n).Value = v(i, 2)
        .Offset(, 2).Resize(n).Value = v(i, 3)
        .Offset(, 3).Resize(n).Value = v(i, 4)
        .Offset(, 4).Resize(n).Value = v(i, 5)
        .Offset(, 5).Resize(n).Value = v(i, 6)
    End With
Next i

Application.ScreenUpdating = True

End Sub

Thank you so much Stephen! It works now
I really appreciate all your help!

Just wanted to say that this has solved a problem I've been mulling over for a while. Thanks very much.