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.