Inserting new rows based on cell value, and copying data to the new rows

[Yann74]

Hi all,

I've been searching a solution to my issue on the board but could not find a working solution that covers my needs.
I'd really appreciate some help to come up with a VBA code that does the following:

I have an Excel file with multiple sheets. In a specific sheet called "xyz" I have data from columns A to BW. Row 1 is for data labels.
The number of rows is variable depending on the source data.

I'd need code to check if column C contains a number or if it's empty.
If it's empty, then proceed to next row.
If it contains a number, then the code should insert the corresponding number of rows below, while copying (and keeping the format) columns G to BW from the origin row to the newly inserted ones.
The loop should process all rows iteratively until there is no data in column A.

For example:
Cell C2 contains "2". Code should insert 2 new rows under row 2 (so 3 and 4) and should copy G2:BW2 to G3:BW3 and G4:BW4 .

Cell C3 (now C5 after the previous rows have been inserted) contains "3". Code should insert 3 new rows under row 5 (so 6, 7 and 8) and should copy G5:BW5 to G6:BW6, G7:BW7 and G8:BW8.

Cell C9 and the the entire row 9 have no data, so the loop should stop.

Many thanks in advance for your kind help!
Yann.

 

[Peter_SSs]

Hi this worked great for me. Except ..

Welcome to the MrExcel board!

This sounds like it might be quite a different question. I suggest that you start a new thread of your own and detail your requirements there. You can provide a link to this thread if you think that it will assist helpers.

 

[Antony93]

Hello @Peter_SSs ,

First off, thanks a lot for everthing in this wonderful thread. I had the initial same requirement of copying a row "X" times. I was able to successfully tweak the code you gave at the beginning.

But if the value "X" is "0", then the code breaks. I do have the value zero and would need to skip those rows from adding additional rows and move to the next one. So far what I have as code is this:

Sub Inert_rows()
  Dim r As Long
 
  For r = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    With Cells(r, 12)
      If IsNumeric(.Value) And Not IsEmpty(.Value) Then
        Rows(r + 1).Resize(.Value).Insert
        Range(Replace("A#:W#", "#", r)).Copy Destination:=Range("A" & r + 1).Resize(.Value)
      End If
    End With
  Next r
End Sub

Would be great to get some inputs as I am very much a newbie trying to get things started.

Best,

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

But if the value "X" is "0", then the code breaks.

Try just adding this red code where shown

If IsNumeric(.Value) And Not IsEmpty(.Value) And .Value > 0 Then

 

[Antony93]

Dear Peter,

the code works perfectly now, thank you very much.

And more importantly, thanks for pointing out the usage of code tags, I will make sure to use them going forward

 

[Peter_SSs]

the code works perfectly now, thank you very much.

You're welcome. Thanks for the follow-up.

 

[antoanand1981]

Hi,
I've same request but, with minor change,

Cell P2 contains "2". Code should insert 2 new rows under row 2 (so 3 and 4) and should copy data from Q2, R3 and paste in specific col "J"

Cell P3 (now P5 after the previous rows have been inserted) contains "3". Code should insert 3 new rows under row 5 (so 6, 7 and 8) and should copy Q2, R3 and S5. (The Max col of my data in 5, that will col, Q, R, S, T, V

Cell P9 and the the entire row 9 have no data, so the loop should stop.