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

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.


Download Practice Workbook


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.

Dataset

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.

How to Insert VBA Code

➤ Secondly, go to Insert > Module.

How to Insert VBA Code

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

Deleting Certain Rows

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.

Deleting Certain Rows

Read More: Excel Shortcut to Delete Rows (With Bonus Techniques)


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

Delete Entire Row from Defined Range When Cell Value is Zero

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.

Delete Entire Row from Defined Range When Cell Value is Zero

Read More: Excel Delete Rows in a Range with VBA (3 Easy Ways)


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

excel macro delete row if cell contains 0 Using InputBox for Defining Range

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.

excel macro delete row if cell contains 0 Using InputBox for Defining Range

Immediately, you’ll get the following output.

excel macro delete row if cell contains 0 Using InputBox for Defining Range

Read More: VBA to Delete Entire Row based on Cell Value in Excel (3 Methods)


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.

excel macro delete row if cell contains 0 across Multiple Sheets

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

excel macro delete row if cell contains 0 across Multiple Sheets

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

excel macro delete row if cell contains 0 across Multiple Sheets

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.

excel macro delete row if cell contains 0 across Multiple Sheets

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

excel macro delete row if cell contains 0 across Multiple Sheets

Eventually, the output for Sheet3 will be as follows.

excel macro delete row if cell contains 0 across Multiple Sheets

Read More: How to Delete Multiple Rows in Excel with Condition (3 Ways) 


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.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo