Insert # of Rows determined by cell

  • Thread starter Thread starter Justin H
  • Start date Start date
J

Justin H

Hello, and thanks to everyone who helps on this forum...I'll get right to it.

This is what I have right now.
A B C D E F G
36201-07 36201 36207 74756 ANNISTON 01 6
35611-14 35611 35614 74768 ATHENS 01 3
36502-04 36502 36504 74769 ATMORE 01 2
36830-49 36830 36849 74772 AUBURN 01 19

What I'm looking to do is insert below the number of rows specified in G.
Can anyone help me out with this?

Thanks,
 
Insert x number of empty rows, huh?

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Variant

Set wks = Worksheets("sheet1")
With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = .Cells(iRow, "G").Value
If IsNumeric(HowMany) Then
.Rows(iRow + 1).Resize(HowMany).Insert HowMany
End If
Next iRow
End With
End Sub
 
Hey Dave, Thanks for the help.

I copied the code you gave and changed the ("sheet1") to my sheet and I
assume that was all I had to change. You were correct that I do have headers
in Row 1 so I left FirstRow=2.

Everything else looked right, but when I compiled the code it highlighted
the 3rd to last line.
..Rows(iRow +1).Resize(HowMany).Insert HowMany

I haven't used most of the code before, is this just a () or something else?

Thanks Again!
 
I don't know how that final HowMany got there!
Remove it so the line looks like:
..Rows(iRow +1).Resize(HowMany).Insert
 
.Rows(iRow +1).Resize(HowMany).Insert HowMany

just change to
.Rows(iRow +1).Resize(HowMany).Insert 'HowMany
or
.Rows(iRow +1).Resize(HowMany).Insert
 
Will do. I thought it looked a little weird, but i just took out the wrong
one. I took out .Resize(HowMany) and left the .Insert HowMany so once this
finally decides to stop running i'll switch it out and hopefully let you know
of the success. Thanks!
 
No Go Fellas, I tried Both and I get Run-Time Error 1004: Application-defined
or object-defined error.
 
What's in the cell that causes the line to fail?

Maybe...

For iRow = LastRow To FirstRow Step -1
HowMany = .Cells(iRow, "G").Value
If IsNumeric(HowMany) Then
If HowMany > 1 Then
.Rows(iRow + 1).Resize(HowMany).Insert
End If
End If
Next iRow
 
Not sure I understand when you said "What's in the cell causing it to fail."
All that is in row G are numbers. I think they range from 1-99.
 
I was asking what was in those cells--counting numbers (1-99 is what you
answered). I was looking for an error caused by having a 0 or a negative number
in those cells.

Is your sheet protected? Unprotect the sheet.

Do you use merged cells? Unmerge the cells.
 
We got it! I searched back through the data page by page and found a lone
wolf throwing everything off. One of the rows decided it wanted to have a -9
thrown in there instead of 9. Thanks a ton for you help!

Here is the Final Code Used to get the job done.

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Variant

Set wks = Worksheets("sheet1")
With wks
FirstRow = 2 'if there headers in row 1, if not use 1 instead of 2???
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = .Cells(iRow, "G").Value
If IsNumeric(HowMany) Then
..Rows(iRow + 1).Resize(HowMany).Insert
End If
Next iRow
End With
End Sub

It's amazing how things work when the data doesn't play games with you.
Thanks again to everyone who helped out.
 
I think I would have kept that check to make sure that the number was always
positive.

If you wanted a little more info (might be helpful if there's another problem):

If IsNumeric(HowMany) Then
If HowMany > 1 Then
.Rows(iRow + 1).Resize(HowMany).Insert
Else
msgbox "Row: " & irow & " has a non-positive number"
End If
Else
msgbox "Row: " & iRow & " has nonnumeric data"
End If
 
Back
Top