Delete entire row if cell is blank or contains [Excel VBA]

Excel VBA code to delete entire row if the cell is blank or empty or cell is ‘xx’ or cell contains ‘xx’.

Case 1: Delete entire row, if cell is empty

The macro checks the cell value, if it is empty or blank, then it deletes the entire row, else the cursor moves to the cell in the next row.

Sub deleteRowEmptyCell()
' This macro checks 100 rows, if it finds blank cell, then it deletes the entire row
Dim numberOfRows
numberOfRows = 1
Do
If ActiveCell.Value = "" Then ' checking if the cell is blank or empty
Selection.EntireRow.Delete ' deleting the row
Else
ActiveCell.Offset(1, 0).Select ' move to cell in the next row 
End If
numberOfRows = numberOfRows + 1
Loop Until numberOfRows > 100
End Sub

Case 2: Delete entire row, if cell value is ‘xx’

Just replace below line of above code

If ActiveCell.Value = "" Then

with

If ActiveCell.Value = "xx" Then

Case 3: Delete entire row if the cell contains ‘xx’ Ex: xx 1, use xx soap

Replace below line of above code

If ActiveCell.Value = "" Then

with

If InStr(ActiveCell.Value, "xx") Then

About the Author

SRINI S

A passionate blogger. Love to share solutions and best practices on wordpress hosting, issues and fixes, excel VBA macros and other apps

Leave a Reply

Your email address will not be published.