How To Blank Out Row if Cell is Blank [Excel VBA]

This tutorial will cover how to delete data within a row, if even one cell in that row is blank. We will cover three techniques:

  • SpecialCells
  • For Each Loop
  • For Loop

Each has its merits.

SpecialCells

Blank cells are shown above in yellow. We will blank out the entire lines related to each cell.

We can use the SpecialCells method of the Cells object. Parameter xlCellTypeBlanks will give us all the blank cells. And for each blank cell, we can clear the contents of the entire row (.EntireRow.ClearContents)

And one thing to note here, is that if there is no blank cell within the used range of the worksheet, the macro will throw an error.

So, we will put a Resume Next statement before the SpecialCells method, to skip over it if there is an error.

And put a Goto 0 statement afterwards, to revert back to normal error handling.

And that’s it for SpecialCells. The drawback here is that it will not detect cells with formulas which returns blank values.

Sub Blank_Out_Row_SpecialCells()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

On Error Resume Next
ws.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.ClearContents
On Error GoTo 0

End Sub

For Each Loop

The last column of the above dataset has formulas. We want to clear out the rows where the formulas return blank values.

The For Each loop will loop through each object in a collection of objects. Here, we want to loop through individual cell within the used range in our worksheet.

And for each cell that we will loop over, we will check whether it is blank or not. If it is we will clear out the contents within that row. 

Sub Blank_Out_Row_ForEach()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

Dim cell As Range
For Each cell In ws.UsedRange
    If cell.Value = "" Then
        cell.Rows(1).EntireRow.ClearContents
    End If
Next cell

End Sub

And one more thing to note here. The For Each loop will loop each column in the first row, before it moves on to the next row.

So, if there are more than one blank cells in a row, the code will clear out the row when it reaches the first blank cell in the row. We don’t need to search for the next blank cell in the same row, because that will not achieve anything. The row has already been blanked out.

But, we can’t really do this modification easily using a For Each loop.

So, moving on to the third technique.

For Loop

The For Loop is a simple and elegant way to solve such an issue. We will build a nested For Loop. The Outer Loop will loop over the rows and for each row, the inner loop will loop over each column.

And, for the issue we discussed during the For Each loop. If we find one blank cell within a row, we don’t want to search for the next column. We should just skip to the next row.

So, after we have found the blank cell, we will put it a Line label to skip out of the inner loop and proceed to the next iteration of the outer loop. Alternatively, we could use an Exit For statement to exit the inner loop.

Sub Blank_Out_Row_For()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

Dim lrow As Long
lrow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
Dim lcol As Long
lcol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column

Dim i As Long, j As Long
For i = 2 To lrow
    For j = 1 To lcol
        If ws.Cells(i, j).Value = "" Then
            ws.Rows(i).ClearContents
            GoTo LineNext
        End If
    Next j
LineNext:
Next i

End Sub

Related Posts