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


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.

delete rows if cell contain zero1

#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.

delete rows if cell contain zero2

#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.

delete rows if cell contain zero3

#4 enter number 0 into the second text box in the Sales section. Click OK button.

delete rows if cell contain zero4

#5 all rows which contain zero values have been filtered out.

delete rows if cell contain zero5

#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.

delete rows if cell contain zero6

delete rows if cell contain zero7

#7 all visible rows should be deleted.

delete rows if cell contain zero8

#8 select Sales column, and go to DATA tab, click Filter command to disable Filter function.

delete rows if cell contain zero9

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.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 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

#5 back to the current worksheet, then run the above excel macro. Click Run button.

delete rows if cell contain zero11

#6 Select one Range that you want to delete row if it contains zero.

delete rows if cell contain zero12

#7 Let’s see the last result:

delete rows if cell contain zero13

Video: Delete Rows If It Contain Zero

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar