Looping Through all Blank Cells

[dhancy]

Hello,

My goal is to find all blank cells in a column, and when I find those cells, I want to delete the entire row.

My data has two blank cells in column B.

In the code below, the first debug statement correctly recognizes the two blank cells.

However, the For loop stops after only one iteration.

Any ideas what I am missing?


Here is my code:

Dim lastRow As Integer
Dim rng As Range
Dim i As Integer

lastRow = Cells(Rows.Count, 2).End(xlUp).Row
i = 0

If lastRow > 2 Then
    Set rng = ActiveSheet.Range("B3:B" & lastRow)
    Debug.Print "Number of empty cells: " & rng.SpecialCells(xlCellTypeBlanks).Count
    For Each rw In rng.SpecialCells(xlCellTypeBlanks).Cells
        Rows(rw.Row).Delete shift:=xlUp
        i = 1 + i
    Next rw
    Debug.Print "Number of empty rows deleted: " & i
End If

Thanks!


Dennis

 

[MikeVol]

Better do like this

    Dim i           As Long

    Dim lastRow     As Long
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    Application.ScreenUpdating = False

    For i = lastRow To 3 Step -1   ' Let's go from the bottom up
        If IsEmpty(Cells(i, 2)) Then Rows(i).Delete shift:=xlUp
    Next i

    Application.ScreenUpdating = True

 

[NoSparks]

perhaps just this would do without looping

    Range("B3", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlBlanks).EntireRow.Delete

 

[dhancy]

perhaps just this would do without looping

    Range("B3", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlBlanks).EntireRow.Delete

This is perfect. It's everything I needed in just one line of code. Thank you !!!!