Quite a Difficult Problem, any Help Please :-)

[stevie3]

Hello Everybody

I am having quite a problem with a spreadsheet I am working on.

I basically need to copy a row, and if the value in cell "V" is greater than 1, then that many rows (as specified in cell "V") need to be inserted below, and the copied row needs to be inserted that many times.

This all needs to occur while still keeping the formatting of the rows that have now moved (as they are linked to another sheet), as well as occuring for all the rows till about 20000.

The macro should also be able to detect when the value in cell "V" is zero, and then simply move on to the next row and go through the process again. Any help please?

 

[Weaver]

Welcome to the board.

I think this ought to do it.

Tested on a very small dataset (34 rows) so I've no idea how long it will take. Run it on a copy of your data and go make a coffee.

Sub test()
    lr = Cells(Rows.Count, "V").End(xlUp).Row
    r = 1
    Application.ScreenUpdating = False
    Do
        v = Val(Cells(r, "V"))
        If v > 0 Then
            Rows(r).Copy
            Rows(r + 1).Resize(v).Insert
            r = r + v + 1
            lr = lr + v
        Else
            r = r + 1
        End If
    Loop Until r > lr
End Sub

HTH

 

[stevie3]

Thank you so much! you are a genius!

 

[Weaver]

Thank you so much! you are a genius!

Thanks for the feedback. That's the second time someone's said that to me this week. The first was in a room full of people and was way more embarrassing!