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

 

[Billman101]

42

 

[Fluff]

If you only have data to day 7, I would expect the Msgbox to have said 21, not 42.
Which means you have data of some sort in those extra rows of col A

 

[Billman101]

Ok I see where you'd get that. The first cell in Col. A that has data is at row 24 so this would put the last cell at row 42.

Bill

 

[Fluff]

In that case try

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

 

[Billman101]

Works like a charm!

Thanks,
Bill

 

[Fluff]

You're welcome & thanks for the feedback