How can I insert # of row(s) below every row equal to number mentioned in last column of that row?

[prashantsharma_03]

Dear All,

I am working on a file where I need to insert row(s) below every row based on a number mentioned in the last column of that data. That means the number of row to be inserted below must be equals to that number. For example, if the number mentioned in that column is 5 than it insert 5 rows below it or if the number is 2 then 2 rows and if number is 0 than no insertion.

I would be very grateful if someone would help me here.

For explanation I am uploading a sample file. Where you can get better idea of my requirement.

WGR, PS

 

[xlstime]

Hi,

Please try below vba code

Sub insertdata()
   
Dim i As Integer
Dim k As Integer

n = Worksheets("Sheet1").Range("E:E").Cells.SpecialCells(xlCellTypeConstants).Count - 1
Range("e1").Offset(1).Select

For i = 1 To n

x = ActiveCell.Row

    v = Cells(x, 5)
   
    If v = 0 Then Exit For Else:
   
   
    Rows(x + 1 & ":" & x + v).Select
    'Range("E3").Activate
    Selection.Insert Shift:=xlDown
   
    Cells(ActiveCell.Row, 5).End(xlDown).Select
   
    Next
   

End Sub

Dear All,

I am working on a file where I need to insert row(s) below every row based on a number mentioned in the last column of that data. That means the number of row to be inserted below must be equals to that number. For example, if the number mentioned in that column is 5 than it insert 5 rows below it or if the number is 2 then 2 rows and if number is 0 than no insertion.

I would be very grateful if someone would help me here.

For explanation I am uploading a sample file. Where you can get better idea of my requirement.

WGR, PS

 

[prashantsharma_03]

Thank you my friend. This is awesome.

 

[prashantsharma_03]

Dear Friends,

This code is working really well. But want to change one thing in it which I am unable to do myself. That is, This row insertion stops working once it encounters 0 or black cell in column E, but now my requirement is that it should not stop with 0 (Just not insert any row below it rather move to next row for check and execution).

Kindly review and help.

Br, PS

 

[Marc L]

Hi !

Just respecting TBTO rule and burning a couple of neurones !​

Sub Demo()
    Application.ScreenUpdating = False
For R& = Cells(1).CurrentRegion.Rows.Count To 2 Step -1
    With Cells(R, 5)
        If .Value > 0 Then Rows(R + 1).Resize(.Value).Insert: .ClearContents
    End With
Next
    Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[prashantsharma_03]

My dear friend, This is awesome.. working absolutely perfect, but it's a bouncer for me. The code completely went off head.

How can I be able to do it like you?

Also what is TBTO Rule?

WGR, PS

 

[Marc L]

TBTO means Think, But Think Object !

It's just respecting Excel object model from his top level
Application (Excel itself), Workbooks, Worksheets,
Cells or Range or Columns or Rows, …
Avoiding slowing down execution 'cause of bad Activate or Select …

Respecting this rule makes code easier !
But my code at Cells and Rows level works only with active worksheet
(but easy to mod to work with only one worksheet even not active) …

And watching what happens when inserting rows :
so the clever way is to start from last row !

For statements you do not know
(CurrentRegion, Resize, ScreenUpdating, With for example) :
all is yet in VBA inner help and with samples !

You can practice first by using Macro Recorder,
in a second time you try to clean its generated code
to produce a kind of code like mine …

There is no secret : practicing, reading then practicing, reading again …
​

 

[prashantsharma_03]

Great Rule and best strategy!! Thanks for sharing it with me.

WGR, PS

 

[Marc L]

Another TBTO sample in this thread :

VBA code to delete multiple hyperlinks throughout multiple worksheets …
​

 

[prashantsharma_03]

Thanks for sharing this Marc. Very informative.. please share more of such threads.

BTW this link clarify that TBTO is KIS i.e., "Keep it Simple". Right??

WGR, PS

 

[Marc L]

KIS is the way combining the two main rules :

• TEBV (Think Excel Before VBA)
• TBTO

Another sample in Finding out Sum value of specific transaction type …

Thinking what functionality exists within Excel to reach a need
can at least avoid a gas factory code as well to not go to a code way !
Many people start directly with VBA but sometimes the same result
can be achieved using a formula, an advanced filter or a pivot table …
​