Inserting Row(s) based on cell Value on same sheet

[Billman101]

Hello,

First time post/reply. I'm a pro at recording macros :wink: but not so much on writing them. I'm trying to insert Row(s) based on cell value(s) that reside within my sheet.

Cell A3 has a value of N = 3 and I'd like to insert N rows at row 5 (two rows lower than 3). Then
at Cell A6 which has a value of N = 26 I'd like to insert N rows at row 8 (again two rows lower) and so on as depicted below:
[TABLE="class: cms_table, width: 229"]
<tbody>[TR]
[TD="align: right"]3[/TD]
[TD]Sunday[/TD]
[TD]2/17/2019[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD]Monday[/TD]
[TD]2/18/2019[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]Tuesday[/TD]
[TD]2/19/2019[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]Wednesday[/TD]
[TD]2/20/2019[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]Thursday[/TD]
[TD]2/21/2019[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]Friday[/TD]
[TD]2/22/2019[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Saturday[/TD]
[TD]2/23/2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any insight you have,
Bill

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Billman101()
   Dim i As Long, Qty As Long
   
   For i = Range("A" & Rows.Count).End(xlUp).Row To 3 Step -3
      Rows(i + 2).Resize(Cells(i, 1)).Insert
   Next i
End Sub

 

[Billman101]

Thanks Fluff,

This seems to work but once it runs it returns an error. When I debug the following is highlighted:

Rows(i + 2).Resize(Cells(i, 1)).Insert

Thanks.

 

[Fluff]

Do you have numbers in every cell from A3 to the end of the column?

 

[Billman101]

Hi Fluff,

Thanks for help.

I've pasted a portion of my sheet below:

[TABLE="width: 245"]
<colgroup><col width="41" style="width: 31pt; mso-width-source: userset; mso-width-alt: 1499;"> <col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <tbody>[TR]
[TD="width: 41, bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: #A9D08E"]Component Asset Group Description (Level 7)[/TD]
[TD="width: 92, bgcolor: #A9D08E"]WO Number[/TD]
[TD="width: 87, bgcolor: #A9D08E"]WO Status[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: black"] [/TD]
[TD="width: 92, bgcolor: black"] [/TD]
[TD="width: 87, bgcolor: black"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="width: 107, bgcolor: yellow"]Sunday[/TD]
[TD="width: 92, bgcolor: yellow"]2/17/2019[/TD]
[TD="width: 87, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: transparent"] [/TD]
[TD="width: 92, bgcolor: transparent"] [/TD]
[TD="width: 87, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: #0D0D0D"] [/TD]
[TD="width: 92, bgcolor: #0D0D0D"] [/TD]
[TD="width: 87, bgcolor: #0D0D0D"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]26[/TD]
[TD="width: 107, bgcolor: yellow"]Monday[/TD]
[TD="width: 92, bgcolor: yellow"]2/18/2019[/TD]
[TD="width: 87, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: transparent"] [/TD]
[TD="width: 92, bgcolor: transparent"] [/TD]
[TD="width: 87, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: #0D0D0D"] [/TD]
[TD="width: 92, bgcolor: #0D0D0D"] [/TD]
[TD="width: 87, bgcolor: #0D0D0D"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="width: 107, bgcolor: yellow"]Tuesday[/TD]
[TD="width: 92, bgcolor: yellow"]2/19/2019[/TD]
[TD="width: 87, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: transparent"] [/TD]
[TD="width: 92, bgcolor: transparent"] [/TD]
[TD="width: 87, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: #0D0D0D"] [/TD]
[TD="width: 92, bgcolor: #0D0D0D"] [/TD]
[TD="width: 87, bgcolor: #0D0D0D"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="width: 107, bgcolor: yellow"]Wednesday[/TD]
[TD="width: 92, bgcolor: yellow"]2/20/2019[/TD]
[TD="width: 87, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: transparent"] [/TD]
[TD="width: 92, bgcolor: transparent"] [/TD]
[TD="width: 87, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: #0D0D0D"] [/TD]
[TD="width: 92, bgcolor: #0D0D0D"] [/TD]
[TD="width: 87, bgcolor: #0D0D0D"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="width: 107, bgcolor: yellow"]Thursday[/TD]
[TD="width: 92, bgcolor: yellow"]2/21/2019[/TD]
[TD="width: 87, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: transparent"] [/TD]
[TD="width: 92, bgcolor: transparent"] [/TD]
[TD="width: 87, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #0D0D0D"] [/TD]
[TD="bgcolor: #0D0D0D"] [/TD]
[TD="bgcolor: #0D0D0D"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="width: 107, bgcolor: yellow"]Friday[/TD]
[TD="width: 92, bgcolor: yellow"]2/22/2019[/TD]
[TD="width: 87, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: transparent"] [/TD]
[TD="width: 92, bgcolor: transparent"] [/TD]
[TD="width: 87, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #0D0D0D"] [/TD]
[TD="bgcolor: #0D0D0D"] [/TD]
[TD="bgcolor: #0D0D0D"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="width: 107, bgcolor: yellow"]Saturday[/TD]
[TD="width: 92, bgcolor: yellow"]2/23/2019[/TD]
[TD="width: 87, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 107, bgcolor: transparent"] [/TD]
[TD="width: 92, bgcolor: transparent"] [/TD]
[TD="width: 87, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #0D0D0D"] [/TD]
[TD="bgcolor: #0D0D0D"] [/TD]
[TD="bgcolor: #0D0D0D"] [/TD]
[/TR]
</tbody>[/TABLE]

What I need to have done is add space based on what's in col. A cells. Then paste in this space data from either an external file or within a tab in this spreadsheet.

The space needed to paste data from Col. A to L.

Many thanks,

 

[Fluff]

Could you please answer my question?

 

[Billman101]

Fluff,

No. As you can see in previous post Col. A only has numbers for each date.

Bill

 

[Fluff]

Oops, I should have said
Do you have numbers in every 3rd cell from A3 to the end of the column?
I ask because the code I supplied works for the data you posted.

 

[Billman101]

Ok. No only every third cell down to the seventh (day). After that there is no data.

Again thanks,
Bill

 

[Fluff]

If you run this

Sub chk()
   MsgBox Range("A" & Rows.Count).End(xlUp).Row
End Sub

What does the Msgbox say?