How to Delete Rows if It Contains Zero in Excel
This post will guide you how to delete rows if it contains zero value in Excel. How do I delete entire rows if one cell is equal to zero with a VBA Macro in Excel.
- Delete Rows If It Contains Zero with Filter Feature
- Delete Rows If It Contains zero with VBA Macro
- Video: Delete Rows If It Contains Zero
Delete Rows If It Contains Zero with Filter Feature
Assuming that you have a list of data in range A1:C7, which contain product name, date and sales. And you want to remove all rows which sales value is equal to zero. How to achieve it. You can use the filter function to achieve the result. Just do the following steps:
#1 select the sales column which contain zero values.
#2 go to DATA tab, click Filter command under Sort & Filter group. And one drop-down arrow is added in the first cell in Sales column.
#3 click drop-down arrow in the first cell of Sales column. And click Number Filters, then select Equals from the sub context menu list. And the Custom AutoFilter dialog will open.
#4 enter number 0 into the second text box in the Sales section. Click OK button.
#5 all rows which contain zero values have been filtered out.
#6 select those rows that filtered out, and right click on it, select Delete Row from the pop-up menu list. And then click Ok button.
#7 all visible rows should be deleted.
#8 select Sales column, and go to DATA tab, click Filter command to disable Filter function.
Delete Rows If It Contain zero with VBA Macro
You can also use an Excel VBA Macro to achieve the same result of Deleting rows if it contain zero or one cell is equal to zero value. Just do the following steps:
#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module.
#4 paste the below VBA code into the code window. Then clicking “Save” button.
Sub DeleteRowIfContainZero()
Set myRange = Application.Selection
Set myRange = Application.InputBox("Select one Range that you want to delete row if it contains zero:", "DeleteRowIfContainZero", myRange.Address, Type:=8)
Do
Set myCell = myRange.Find("0", LookIn:=xlValues)
If Not myCell Is Nothing Then
myCell.EntireRow.Delete
End If
Loop While Not myCell Is Nothing
End Sub
#5 back to the current worksheet, then run the above excel macro. Click Run button.
#6 Select one Range that you want to delete row if it contains zero.
#7 Let’s see the last result:
