Frequently you may need to omit unnecessary cells containing specific cell values that are no longer 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 a row if a cell contains 0 using Excel Macro with a proper explanation.
How to Delete Row Using Macro If Cell Contains 0 in Excel: 4 Methods
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 a 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.
Read More: How to Use Macro to Delete Rows Based on Criteria in Excel
2. Delete the Entire Row from the Defined Range When the 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.
Read More: How to Apply VBA Code to Delete Rows Based on Multiple Cell Value
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 utilize 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.
Read More: How to Use VBA to Delete Empty Rows in Excel
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 a 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 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.
Read More: Excel VBA to Delete Row If Cell Contains Partial Text
Download Practice Workbook
Conclusion
This is how you might delete a row if the cell contains 0 in Excel Macro. Choose any method based on your requirements. Anyway, if you have queries or recommendations, please share them in the comments section.
