Insert row after blank cell encountered

[KG Old Wolf]

I have a large table with infrequent blank cells in a single column. I want
to located these and insert a new row directly after that blank cell's row.
This isn't working ....

Sub InsertBlankLine()
'
FinalRow = Cells(Rows.Count, 8).End(xlUp).Row
'
For Counter = 1 To FinalRow
Set curCell = Worksheets("Sheet1").Cells(Counter, 6)
If curCell.Value < 1 Then curCell.Value = FinalRow
'
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
'
Next Counter
End Sub


Row 8 contains the first blank cell condition but the macro inserts 13 blank
rows right after the first row. (13 is the value of FinalRow).

I have spent 4 hours on this trying every combination I can think of. Your
help is appreciated.

 

[Don Guillett]

Best to work from the bottom up I can't figure out what you are trying to do
for i= cells(rows.count,8).end(xlup).row to 2 step -1

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.

 

[KG Old Wolf]

Hi Don,

I acquire the total rows count fine and place the value in the blank cells
when encountered. That worked fine. The problem began when I attempted to
add code to insert a blank line directly after encountering the blank cell.
(By posting the "FinalRow" value in the blank cell, it gave me confirmation I
was looping through the data set properly). As I said, it was only when I
started using the "Insert row" code that I had trouble.

I understand that working from the bottom up can sometimes be helpful but I
don't see an advantage in this case and, it is easier conceptually for me to
work top down.

I won't take you up on your offer though I greatly appreciate it.

Best regards,
Ken

 

[Don Guillett]

Sub insertblanklineSAS()
finalrow = Cells(Rows.Count, 8).End(xlUp).Row
For i = finalrow To 1 Step -1
If Len(Application.Trim(Cells(i, 6))) < 1 Then
Rows(i + 1).Insert
End If
Next i
End Sub

 

[Rick Rothstein]

If I understand your question correctly (all you want to do is insert the
blank lines, not assign the FinalRow value to the blank cells), then this
code should do what you want...

Sub InsertBlankLines()
Dim FinalRow As Long
With Worksheets("Sheet1")
On Error Resume Next
FinalRow = .Cells(.Rows.Count, 8).End(xlUp).Row
.Range("F1:F" & FinalRow).SpecialCells(xlCellTypeBlanks). _
Offset(1).EntireRow.Insert
End With
End Sub

I'd advise that you copy/paste the above, but if you decide to type it in
instead, then pay close attention to the leading dots that I have used (they
make the range refer back to the object of the With statement (Sheet1 in
this case).

 

[KG Old Wolf]

I owe you one Don - you taught a few lessons here.... thank you

Lesson 1 - how to insert the rows -- Yes, IT WORKED!

Lesson 2 - LISTEN TO OTHERS.... you said to go from bottom up, I chose not
to - couldn't get it to work... you did.

Thank you very much,
Ken

 

[Rick Rothstein]

If you are interested, take a look at the solution I posted for a
non-looping method of doing the insertions.