How to Delete Row Using Macro If Cell Contains 0 in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Frequently you may need to omit unnecessary cells containing specific cell values that are no more useful for you. For example, you might delete those rows having the cell value of Zero. Macro provides you with the most fruitful methods to accomplish the task quickly. In this article, I’ll demonstrate to you 4 effective methods to delete row if a cell contains 0 using Excel Macro with proper explanation.

Table of Contents hide

Download Practice Workbook

4 Methods to Delete Row Using Macro If Cell Contains 0 in Excel

1. Macro to Delete Certain Rows If Cell Contains 0

2. Delete Entire Row from Defined Range When Cell Value is Zero

3. Using InputBox for Defining Range to Delete Entire Row

4. Delete Row If Cell Contains 0 across Multiple Sheets

Conclusion

Download Practice Workbook

Deleting Row If Cell Contains 0.xlsm

4 Methods to Delete Row Using Macro If Cell Contains 0 in Excel

The below picture depicts today’s dataset where the Number of Visits is given along with the Name of Sites and Date. More importantly, some sites have a cell value (Number of Visits) of 0. In such a situation, you might want to delete rows having the cell value of 0.

Let’s explore the methods.

1. Macro to Delete Certain Rows If Cell Contains 0

Let’s imagine a case where you have to delete rows in a specific range having the cell value of 0. That means you want to delete certain rows containing the cell value 0 from the whole dataset.

In this case, you may follow this method as described in a step-by-step process.

Step 01: Inserting a Module

➤ Firstly, open a module by clicking Developer > Visual Basic.

➤ Secondly, go to Insert > Module.

Step 02: Copying the VBA Code

Now, just copy the following code and the detailed explanation is available after the code.

Sub Delete_CertainRows()
Dim Sht As Worksheet
Dim last as Long
Dim i As Long
Set Sht = ActiveSheet
    last = Cells(Rows.count, "A").End(xlUp).Row
    For i = last To 14 Step 1
        If Cells(i, "D").Value = "0" Then
                Cells(i, "D").EntireRow.Delete
        End If
    Next i

End Sub

Macro Code Explanations

I declared Sht as Worksheet to define the current worksheet. In addition, I also declared last and i as Long to store the row number.
Then I turned on the current sheet by setting the ActiveSheet.
Immediately, I assigned the last to Cells(Rows.count, “A”).End(xlUp).Row to return the last row as the top cell of the bottom column.
Later, I utilized For Loop where i was assigned as the last To 14 Step 1. That means the loop would run till Row 14 and the increment was 1 for every row.
Lastly, I added an If statement to delete the row having the cell value of 0. And Delete is attached to delete the entire row when the statement is true.

Step 03: Running the VBA Code

When you run the code (the keyboard shortcut is F5 or Fn + F5), you’ll get the following output.

2. Delete Entire Row from Defined Range When Cell Value is Zero

If you have a specified range of cells of the dataset from where you want to delete rows containing any cell value of 0, this method will provide you with the output quickly.

For doing that copy the following code into the newly created module.

Sub DeleteRow_DefinedRange()
 
Dim Rng As Range
 
For Each Rng In Range("D5:D14")
    If Rng.Value = "0" Then
        Rng.EntireRow.Delete
    End If
Next Rng
 
End Sub

Macro Code Explanations to Delete Row for Defined Range

Firstly, I declared Rng as Range.
Then I assigned For Each statement to define each element of the pre-defined range i.e. D5:D14.
Along with that, I also added an If statement and Delete to delete the entire row when the statement is true.

However, if you run the code, the output will look as follows.

Similar Readings:

3. Using InputBox for Defining Range to Delete Entire Row

But if you don’t have the pre-defined range, and you want to select the range after running the code, you may utilise the InputBox. Such a method will be handy when you have a larger dataset and you have to execute the task repetitively.

To utilize the method, just copy the following code.

Sub DeleteRow_UsingInputBox()
    Set Rng = Application.Selection
    Title = "Delete Row Using InputBox"
    Set Rng = Application.InputBox("Please Select the Cell Range:", Title, Rng.Address, Type:=8)
    Do
        Set cell = Rng.Find("0", LookIn:=xlValues)
        If Not cell Is Nothing Then
           cell.EntireRow.Delete
        End If
    Loop While Not cell Is Nothing
End Sub

Macro Code Explanations to Delete Row Using InputBox

Here, I set Rng utilizing InputBox
Then I assigned Do Loop to repeat and transfer the control of the If Not statement.
Meanwhile, I specified the cell using the Find to find 0.
In addition, I added an If Not statement with the Delete to delete row when the statement is true.

Next, you’ll see the following dialog box to enter the range from where you want to delete rows with specific criteria. Then, type the $D$5:$D$14 manually or drag down the cursor from D5 to till D14 cell.

Immediately, you’ll get the following output.

4. Delete Row If Cell Contains 0 across Multiple Sheets

The above 3 methods were limited within the active worksheet. However, if your requirement is to execute the task for multiple sheets. I mean if you want to delete row if the cell contains 0 across multiple sheets, you may utilize the current method.

For example, there are 3 separate working sheets available in the dataset. Sheet1 includes the same information as in the dataset. But Sheet2 has a slightly different Number of Visits.

Similarly, the following picture denotes the different information about Sheet3.

Now, copy the following code to carry out the task for the above-mentioned 3 sheets.

Sub DeleteRows_MultipleSheets()
Dim Sht As Worksheet
Dim last As Long
Dim i As Long
For Each Sht In Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
    Sht.Activate
    last = Cells(Rows.count, "A").End(xlUp).Row
    For i = last To 14 Step 1
        If Cells(i, "D").Value = "0" Then
                Cells(i, "D").EntireRow.Delete
        End If
    Next i
 Next Sht
End Sub

Macro Code Explanations to Delete Row across Multiple Sheets

I declared Sht as Worksheet. In addition, I also declared last and i as Long to store the row number.
Then I specified the 3 sheets with their name using the Worksheets And For Each statement is set to define each element.
The rest are similar as shown in the first method.

After running the code, you’ll get the output in the case of Sheet1.

Likewise, you’ll get the following output in the case of Sheet2.

Eventually, the output for Sheet3 will be as follows.

Conclusion

This is how you might delete a row if the cell contains 0 in Excel Macro. Choose any method based on your requirement. Anyway, if you have queries or recommendations, please share them in the comments section.