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

K

Keith412

New Member
Joined
Mar 2, 2008
Messages
41
[TABLE="width: 500"]
<tbody>[TR]
[TD]Location
[/TD]
[TD]#
[/TD]
[TD]Month
[/TD]
[TD]Year
[/TD]
[/TR]
[TR]
[TD]NYC
[/TD]
[TD]5
[/TD]
[TD]July
[/TD]
[TD]2019
[/TD]
[/TR]
[TR]
[TD]Florida
[/TD]
[TD]3
[/TD]
[TD]August
[/TD]
[TD]2019
[/TD]
[/TR]
[TR]
[TD]Mass
[/TD]
[TD]1
[/TD]
[TD]October
[/TD]
[TD]2019
[/TD]
[/TR]
</tbody>[/TABLE]
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:

[TABLE="width: 167"]
<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>[TR]
[TD="width: 97, bgcolor: #B1E0A4"]Location[/TD]
[TD="width: 43, bgcolor: #B1E0A4"] # [/TD]
[TD="width: 46, bgcolor: #B1E0A4"]Month[/TD]
[TD="width: 36, bgcolor: #B1E0A4"]Year[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent"]2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent"]2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent"]2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent"]2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent"]2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"]Jun[/TD]
[TD="bgcolor: transparent"]2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"]Jun[/TD]
[TD="bgcolor: transparent"]2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"]Jun[/TD]
[TD="bgcolor: transparent"]2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent"]2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Massachusetts[/TD]
[TD="bgcolor: transparent"] 1 [/TD]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent"]2019[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
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

[TABLE="width: 138"]
<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>[TR]
[TD="width: 109, bgcolor: transparent"]Location[/TD]
[TD="width: 74, bgcolor: transparent"]#[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Location[/TD]
[TD="bgcolor: transparent"]#[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
How about
Code: 
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
 
Upvote 0
There you go I asked a question and before I could get back you have a answer.
 
Upvote 0
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"
 
Upvote 0
In that case try
Code: 
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
 
Upvote 0
Well this is how I would have done it:
Not sure what you new request wants.
Code: 
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
 
Upvote 0
What error message do you get?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Solved
Inserting new rows based on cell value, and copying data to the new rows
2 3 4
Replies
37
Views
37K
Joe4
Joe4
E
  • Question Question
Yearfrac problem
Replies
3
Views
5K
EleanorSykes1989
E
J
  • Question Question
pulling data from one chart to another
Replies
1
Views
811
jb00976
J
S
  • Question Question
Conditional Formating Required for changing Dates based on 2 different cell dates
Replies
1
Views
792
Toadstool
Toadstool
N
  • Question Question
How to Create Multiple Reports from One Sheet
Replies
1
Views
2K
Logit
Logit

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top