HELP...For Next...Loop is not working right 1

[Nick34]

Is there any reason why this code would not delete rows that have empty cells in the 3rd column after being run only once???

It must be run about 6 or 7 times before all of the rows with empty cells are deleted.

Sub RemoveBlanks()

Dim oCell As Object

Worksheets("SO2REMOVAL1&quot.Select

Application.Goto "R2C3"

Range(Selection, Selection.End(xlDown)).Select

For Each oCell In Selection

If oCell = "" Then
'Debug.Print oCell.Offset(0, -1).Value
Rows(RowIndex:=(oCell.Row)).Delete
End If

Next oCell

End Sub

 

[JohnYingling]

Because you are deleting from "front to back". After you delete the current row, the next row becomes the current row and the "Next" promptly "leapfrogs" it. I perform "deletes" from collections from "back to front"

Can you do

For i = Selection.Count - 1 to 0 Step -1
    oCell = Selection(i)    
    If oCell = "" Then
      'Debug.Print oCell.Offset(0, -1).Value
       Rows(RowIndex:=(oCell.Row)).Delete
   End If
Next

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[Nick34]

Thank you. I had no idea you could work backward. Could you just explain why you subtracted 1 from the count and went to 0? Why not just go from the count to 1?

 

[jcrater]

How about:
1) sort by column 3
2) use the xlUp/xlDown trick to determine the first empty row
3) delete a range of rows in one step

 

[DrBowes]

Thanks to Tony Jollans for this one - to delete all blanks in column c:

Columns("C:C&quot.SpecialCells(xlCellTypeBlanks).EntireRow.Delete