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.

1. 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:

Step1: select the sales column which contain zero values.

delete rows if cell contain zero1

Step2: 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.

delete rows if cell contain zero2

Step3: 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.

delete rows if cell contain zero3

Step4: enter number 0 into the second text box in the Sales section. Click OK button.

delete rows if cell contain zero4

Step5: all rows which contain zero values have been filtered out.

delete rows if cell contain zero5

Step6: 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.

delete rows if cell contain zero6
delete rows if cell contain zero7

Step7: all visible rows should be deleted.

delete rows if cell contain zero8

Step8: select Sales column, and go to DATA tab, click Filter command to disable Filter function.

delete rows if cell contain zero9

2. 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:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

delete rows if cell contain zero10
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

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

delete rows if cell contain zero11

Step6: Select one Range that you want to delete row if it contains zero.

delete rows if cell contain zero12

Step7: Let’s see the last result:

delete rows if cell contain zero13

3. Video: Delete Rows If It Contain Zero

This tutorial video will show you how to remove rows that contain zeros throught two effective methods in Microsoft Excel.

4. SAMPLE FIlES

Below are sample files in Microsoft Excel that you can download for reference if you wish.

Leave a Reply