Inserting row based on value in cell in Column C

[Anonymous CHief]

Hi,

I need a macro that will take values in column C and add that number of rows
below that cell as the number in that cell.

An example:
A cell in C2 for instance has the value 3. I want the macro to then read the
3 in cell C2 and then add 3 rows below cell C2 or row 2.

Please help. This is like a two step process that I need help with. Thank
you.

Anonymous Chief

 

[richardreye]

Not sure if the IsNumeric actually helps that much but
here is some code that will work

Sub InsertRows()

For Each cell In Range("C:C")
If IsNumeric(cell.Value) And cell.Value >= 1 Then
Range(cell.Offset(1, 0).EntireRow, _
cell.Offset(cell.Value, 0).EntireRow).Insert
xlDown
End If
Next cell

End Sub

Cheers

 

[Anonymous Chief]

Hi richard,

Thank you for the code. I tried it but I get a compile error and the
visualbasic editor highlights "xlDown" in blue, and then highlights "Sub
InsertRows()" in yellow. I know you are onto something real good here.
Please help.

Later

 

[Tom Ogilvy]

Sub InsertRows()
Dim lastRow as Long, cell as Range
Dim i as Long
lastrow = cells(rows.count,"C").End(xlup).row + 1
for i = lastrow to 2 step -1
set cell = cells(i,"C")
If IsNumeric(cell(0,1).Value) Then
If cell(0,1).Value >= 1 Then
cell.Resize(cells(0,1).Value) _
.EntireRow.Insert
End if
End If
Next cell
End Sub

 

[David McRitchie]

Richard's solution is testing the original and the inserted
rows in Column C. Since the inserted rows do not have
a value it works but it would be much better to
use to use Step -1 and start from the bottom and work
up. Checking inserted rows is not the most efficient.
And the test for numeric that he questioned if it would be
needed.

 

[Anonymous Chief]

I tried Tom's solution, but it also does not run, prompts me to debug. Guys,
you have to know that I am not that familiar with creating macros. I can
only insert the code in the visual basic editor and run it, but am nt
capable of debugging it. Tom and Richard have been great, but it still does
not work. I don't know if it will help. I am using Excel 2003. Please help
me, and thank you all for your input. I guess if smeone could try out
somthing based on David McRitchie's suggestion.

Thanks

 

[David McRitchie]

Tom gave you a complete macro that includes what I said,
what you see in the thread in chronological order is not necessarily
the order that it "should be read" [or even ignored for something more complete].

There were a couple of typos
(if it were my code they'd be errors and I'd say typos)

Sub InsertRows()
Dim lastRow As Long, cell As Range
'Tom Ogilvy, 2005-03-09 programming --corrected
Dim i As Long
lastRow = Cells(Rows.Count, "C").End(xlUp).row + 1
For i = lastRow To 2 Step -1
Set cell = Cells(i, "C")
If IsNumeric(cell(0, 1).Value) Then '-- correction
If cell(0, 1).Value >= 1 Then
cell.Resize(cell(0, 1).Value) _
.EntireRow.Insert
End If
End If
Next i '--correction
End Sub