Macro for inserting a certain # of rows

[Kevin_]

Hi - any help writing this macro is greatly appreciated. I have about 1000 rows of data (Row 1 is titles).

In Column L, I've used a countif on another tab to figure out the # of rows I want to insert below each row from the orginal data.

So, cell L2 may have the value 15, cell L3 has 9, cell L4 has 1, and cell L5 has zero.

I want the macro to insert 15 rows below row 2, then insert 9 rows below (what used to be) row 3, then 1 row below (what used to be) row 4, and not insert any rows below row 5.

I'd like this to work all the way to the end of my data.

I think this is pretty simple VBA but I'm a beginner. Thanks very much.

 

[Tom Urtis]

So just to be clear, you have numbers in some cells in column L that represent how many rows you want to insert below those number-containing cells.

First, is that correct, and second for efficiency of execution purposes, are those numbers in column L being returned by formulas in those cells (you mentioned COUNTIF) or are those numbers in column L there because they were manually entered, meaning column L has no formulas.

 

[Kevin_]

Yes and yes. For now, I want to insert 15 blank rows below row2. The value in cell L2 is a formula -a countif based off another tab that, in this case, yielded a result of 15. Thank you

 

[Tom Urtis]

Try this on a copy of your worksheet:

Sub InsertRowz()
If WorksheetFunction.CountA(Columns(12)) > 1 Then
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In Columns(12).SpecialCells(3)
Rows(cell.Row + 1).Resize(cell.Value).Insert
Next cell
Application.ScreenUpdating = True
End If
End Sub

The first line has a > 1 element assuming cell L1 contains a header label. If that is not the case, change > 1 to > 0.

 

[Kevin_]

I got an application-defined or object-defined error. Any thoughts?
Thanks

 

[Tom Urtis]

Hmmm.it worked without errors when I tested it. Is the sheet protected or are there any merged cells

 

[Kevin_]

No, but when I hit debug, it seems to not like something to do with the first line (with Then at the end). Could be something I am doing wrong as I'm a little VBA challenged.

I did copy and paste but also - when you have Columns(l2) - it looks like the number 12. I assume it is L2, though.

 

[Tom Urtis]

It is 12 as in twelve as in the twelfth column which is column L. Just copy and paste my macro as is, into a standard module, and it should work for you as it did for me.

 

[Kevin_]

same error using twelve. Not sure what's wrong - I copied/pasted

 

[mikerickson]

Inserting, like deleting, works best from the bottom up

Dim i As Long

With Sheet1.Range("L:L")
    For i = .Cells(.Rows.Count, 1).End(xlUp).Row to 2 Step - 1
        .Cells(i +1, 0).Resize(.Cells(i, 1), 1).EntireRow.Insert
    Next i
End With