auto insert row based on cell value

[tomhd]

hi guys - sorry this is second post (same title but different querry)

I want a macro to automatically insert blank row(s) based on the cell values in column A. Blank rows to be inserted below the the row with that value. e.g.

row -value
A1 - 0
A2 - 1
A3 - 0
A4 - 2
A5 - 4
A6 - 0
and so on

I want a macro to get following result:

row -value
A1 - 0
A2 - 1
A3 - blank (1 new row inserted)
A4 - 0
A5 - 2
A6 - blank (1 new row inserted)
A7 - blank (1 new row inserted)
A8 - 4
A9 - blank (1 new row inserted)
A10 - blank (1 new row inserted)
A11 - blank (1 new row inserted)
A12 - blank (1 new row inserted)
A13 - 0

Any help is appreciated. Thanks

 

[njimack]

Something like this?

Sub Insert()
    Dim End_Row As Long, n As Long, Ins As Long
    End_Row = Range("A" & Rows.Count).End(xlUp).Row

    For n = End_Row To 1 Step -1

        Ins = Cells(n, 1).Value

        If Ins > 0 Then Range("A" & n + 1 & ":A" & n + Ins).EntireRow.Insert

    Next n
End Sub

 

[Richard Schollar]

Hi

Give this a go:

Sub Test()
Dim lngLastRow As Long, i As Long
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lngLastRow To 1 Step -1
    With Cells(i, 1)
        If .Value >= 1 Then
            .Resize(.Value).Offset(1).EntireRow.Insert xlShiftDown
        End If
    End With
Next
End Sub

 

[tomhd]

both worked great
thank you all

 

[mjrexcelnoob]

If I want to do this same thing but instead of it being based off of a number in a cell, I want it based off of a word (in this case, "Manager (3029)") what format do I use?