Visit my Youtube Channel
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:
- For Each Loop
- For Loop
Each has its merits.
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.
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