Insert # Rows based on numeric cell value and copy entire row to all inserted rows help

[Keith412]

Location	#	Month	Year
NYC	5	July	2019
Florida	3	August	2019
Mass	1	October	2019

<tbody>
</tbody>

I have a workbook with the above information:


I would want it to look at column B, (#) and then insert 5 rows where the #= 1, so 1,1,1,1, instead of the 5:

Location	#	Month	Year
New York	1	Jul	2019
New York	1	Jul	2019
New York	1	Jul	2019
New York	1	Jul	2019
New York	1	Jul	2019
Florida	1	Jun	2019
Florida	1	Jun	2019
Florida	1	Jun	2019
Florida	1	Jul	2019
Massachusetts	1	Jul	2019

<colgroup><col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="43" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1572;"> <col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"> <col width="36" style="width: 27pt; mso-width-source: userset; mso-width-alt: 1316;"> <tbody> </tbody>

 

[My Aswer Is This]

I think your sampled Data is flawed.

In the top sample you have NYC but after script runs you want New YorK

And the same with Mass

And why does Florida and August get changed to Florida and June

And Mass from October to July

 

[Keith412]

You are right, when I manually manipulated the file it didn't create the outcome I wanted. Here is a simplified version of how its currently organized versus what I want to accomplish

Location	#
A	4
B	1
C	2
Location	#
A	1
A	1
A	1
A	1
B	1
C	1
C	1

<colgroup><col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <tbody> </tbody>

 

[Fluff]

How about

Sub Keith412()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, rr As Long
   
   Ary = Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * 1000, 1 To 4)
   
   For r = 2 To UBound(Ary)
      For c = 1 To Ary(r, 2)
         rr = rr + 1
         Nary(rr, 1) = Ary(r, 1)
         Nary(rr, 2) = 1
         Nary(rr, 3) = Ary(r, 3)
         Nary(rr, 4) = Ary(r, 4)
      Next c
   Next r
    Range("G1:J1").Value = Application.Index(Ary, 1, 0)
    Range("G2").Resize(rr, 4).Value = Nary
End Sub

 

[My Aswer Is This]

There you go I asked a question and before I could get back you have a answer.

 

[Keith412]

worked perfectly on my sample, but struggling to apply it to my full data set. Full dataset has 11 columns (A1 to K1) an the # is located in column "G"

 

[Fluff]

In that case try

Sub Keith412()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, j As Long, rr As Long
   
   Ary = Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * 1000, 1 To 11)
   
   For r = 2 To UBound(Ary)
      For j = 1 To Ary(r, 7)
         rr = rr + 1
         For c = 1 To UBound(Ary, 2)
            Nary(rr, c) = Ary(r, c)
            Nary(rr, 7) = 1
         Next c
      Next j
   Next r
    Range("M1:W1").Value = Application.Index(Ary, 1, 0)
    Range("M2").Resize(rr, 11).Value = Nary
End Sub

 

[My Aswer Is This]

Well this is how I would have done it:
Not sure what you new request wants.

Sub Insert_Rows()
'Modified 12/20/2018 11:40:02 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 2 Step -1
    Rows(i).Offset(1).Resize((Cells(i, 2).Value) - 1).Insert
    Rows(i).Offset(1).Resize((Cells(i, 2).Value) - 1).Value = Rows(i).Value
Next
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(2, 2).Resize(Lastrow - 1).Value = "1"
Application.ScreenUpdating = True
End Sub

 

[Keith412]

Thank you, but it seems to debug on the following:

Nary(rr, c) = Ary(r, c)

 

[Fluff]

What error message do you get?