Insert X rows below row Y based on value in row Y

lwhyatt

New Member
Joined
May 26, 2010
Messages
29
How can I get Excel to insert x rows below a row based on the value of a cell in that row?

eg if in row 1 cell B1 contains '5' I want 5 rows inserted.

I have a number of rows with different values so I need different gaps inserted between rows.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,416
Does this work for you?

Code:
Sub addrows()


Dim rownum As Long
Dim addrow As Long


rownum = 1


Do Until Cells(rownum, 2).Value = ""
addrow = Cells(rownum, 2).Value
Rows(rownum + 1 & ":" & addrow + rownum).Insert Shift:=xlDown
rownum = rownum + addrow + 1
Loop


End Sub
 

lwhyatt

New Member
Joined
May 26, 2010
Messages
29
Great thanks. It's adding one too many rows each time though as I want to re-use the first row. What do I change if the cells specifying the number of rows to add are in I4:I439 ?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try this

Code:
Sub Macro2()
    Dim i As Long, n As Long
    For i = Range("I" & Rows.Count).End(xlUp).Row To 4 Step -1
        n = Cells(i, "I").Value
        If n > 0 Then Rows(i + 1 & ":" & i + n).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,201
Messages
5,527,386
Members
409,759
Latest member
KCH

This Week's Hot Topics

Top