Excel: delete row if empty cell condition is true across multiple columns

Question

I have a data set in Excel that i need to filter out rows where no data exists (i.e.- empty cell). It isn't as easy as deleting a row that has an empty cell in one column but rather if there are empty cells in three columns the entire row should be deleted. What macro would be required to execute this?

Pleas advise.

Answer 1

Here's an example where the columns you want to check are A, B, and C.

Option Explicit
Sub DeleteIfBlanks()
    Dim r As Long
    Dim rng As Range
    With ActiveSheet
        For r = 1 To .UsedRange.Rows.Count
            If LenB(.Cells(r, 1)) = 0 Then
            If LenB(.Cells(r, 2)) = 0 Then
            If LenB(.Cells(r, 3)) = 0 Then
                If rng Is Nothing Then Set rng = .Cells(r, 1) Else Set rng = Union(rng, .Cells(r, 1))
            End If
            End If
            End If
        Next
        If Not rng Is Nothing Then rng.EntireRow.Delete
    End With
End Sub

You need to change the statement LenB(.Cells(r, 1)) so that the 1 corresponds to the number of whatever column you want to check for blanks.

---

To other VBA folks out there: I nest my IF statements for the very minor potential speed improvement. I union just the first cell and rollup to entirerow at the end because I'm lazy and I know cell notation better than row notation. If there are any improvements that are functional and not just stylistic, let me know.