Excel VBA delete entire row if both columns B and C are blank

Question

I'm trying to delete an entire row in excel if column B and C are blank for that row. I have this vba code that deletes an entire row if the whole row is blank. How can I only delete the row if B and C have no value?

Thank you

 Sub DeleteBlank()

 Dim rng
 Dim Lastrow As Integer
 Set rng = Nothing

 Lastrow = Range("A" & Rows.Count).End(xlUp).Row

For Each i In Range("B1:B" & Lastrow)
    If Application.CountA(i.EntireRow) = 0 Then

        If rng Is Nothing Then
            Set rng = i
        Else
            Set rng = Union(rng, i)
        End If
    End If
Next i
MsgBox (Lastrow)
If Not rng Is Nothing Then
    rng.EntireRow.Delete
End If

End Sub

--Update--

The problem is solved. Thanks to izzymo and sous2817

Here is the current code

Sub DeleteBlank()

 Dim i As Integer
 Dim Lastrow As Integer

 Lastrow = Range("A" & Rows.Count).End(xlUp).Row
 MsgBox (Lastrow)
 For i = Lastrow To 2 Step -1
   If Trim(Range("B" & i).Value) = "" And Trim(Range("C" & i).Value) = "" Then
    Range("B" & i).EntireRow.Select
    Selection.Delete

    End If

  Next i

 MsgBox "Done"

 End Sub

one little nitpick in your solution (other than you're using a loop...look in to using an autofilter instead). Combine your select and delete row. Those two rows should be: Range("B" & i).EntireRow.Delete — sous2817

sous2817 sous2817 · Accepted Answer · 2015-03-09T14:27:28

As asked for, here is a way to do it without looping:

Sub NoLoopDelete()
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
    With Sheet1.Range("A1:I" & lr)
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="="
        .AutoFilter Field:=3, Criteria1:="="
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
End Sub

The results should be the same, but this way should be faster, especially if you have a lot of rows. Obviously, change the column reference to suit your layout and feel free to fancy it up w/ some error checking,etc.

Excel VBA delete entire row if both columns B and C are blank

2 Answers