Rafiul Haq
Data is getting bigger each and every day. We need to find ways to view large datasets easily and using Excel VBA Macros can save a ton of time. In this article, we will show you 3 VBA Macro to hide rows with zero values in Excel.
Download Practice Workbook
3 Suitable Macros to Hide Rows with Zero Values in Excel
To demonstrate our methods, we have selected a dataset with 3 columns: “Product,” “Model”, and “Quantity Sold.” Basically, this dataset lists the number of products sold in a particular electronics retail store. Our aim is to formulate VBA Macro codes to hide rows with zero values. Moreover, we can see that there are two rows that have 0 values – rows 6 and 8. We will use this dataset for the first two codes and slightly alter it for the last method.
Before jumping on to the step-by-step guides, you need to make sure that the Developer tab is turned on in the Ribbon. If it is not turned on, then you can turn it on simply by following these steps:
- Firstly, File → Options → Customize Ribbon tab → select Developer.
- Then, press OK.
1. Applying Macro InputBox to Hide Rows with Zeros in Excel
For the first method, we will use an InputBox to ask the user for the cell range in VBA code to hide rows with zero values. Moreover, there will be a For Each Next Loop in this Macro. Without further ado, let us jump into the step-by-step guide.
- First, we bring up the VBA Module window, where we type our codes.
- To do this, from the Developer tab → select Visual Basic. Alternatively, you can press ALT+F11 to bring up this too.
- So, the VBA window will pop up.
- Next, from the Insert tab, select Module.
- Here, we insert VBA code to hide rows with zero values.
- After that, type the following code inside the VBA Module window.
Option Explicit
Sub Hide_Rows_Zero_InputBox()
Dim cRange As Range
Dim qq As Range
Set cRange = Application.InputBox("Specify the Cell Range", _
"ExcelDemy", Type:=8)
For Each qq In cRange
If qq.Value = "0" Then
qq.EntireRow.Hidden = True
End If
Next
End Sub
VBA Code Breakdown
- First, we are calling our Sub procedure Hide_Rows_Zero_InputBox.
- Then, we declare the variable types.
- Afterward, we define the source cell range as an InputBox. Here, type 8 denotes the input should be Range type only. Moreover, if we select nothing, then the code will stop.
- Then, we use a For Next Loop to go through each cell of our selected range.
- After that, if the value of a cell is different than the previous cell, we hide rows with zero values.
- Thus, this code works.
- So, our code will execute and it will ask for the cell range to hide rows with zero values.
- Then, select the cell range D5:D10.
- Lastly, press OK.
- By doing so, the code has hidden rows 6 and 8.
Read More: How to Hide Zero Data Labels in Excel Chart (4 Easy Ways)
Similar Readings
- How to Ignore Blank Series in Legend of Excel Chart
- Excel IFERROR Function to Return Blank Instead of 0
- How to Apply VLOOKUP to Return Blank Instead of 0 or NA
- Remove Zeros in Front of a Number in Excel (6 Easy Ways)
- How to Hide Chart Series with No Data in Excel (4 Easy Methods)
2. Using VBA Macro Without InputBox to Hide Rows with Zero Values
In this section, we will predefine our cell range in the VBA code to hide rows with zero values in Excel.
Steps:
- Firstly, as shown in the first method, bring up the Module window.
- Secondly, type the following code inside that.
Option Explicit
Sub Hide_Rows_Zero_2()
Dim qq As Range
For Each qq In Range("D5:D10")
If Not IsEmpty(qq) Then
If qq.Value = 0 Then
qq.EntireRow.Hidden = True
End If
End If
Next
End Sub
VBA Code Breakdown
- First, we are calling our Sub procedure Hide_Rows_Zero_2.
- Then, we declare the variable types.
- Then, we use a For Next Loop to go through each cell of our selected range, which is D5:D10.
- After that, if the value of a cell is “0”, it hides the EntireRow.
- Thus, this code works to hide rows in Excel whenever zero values appear.
- Then, as shown in method 1, we Save and Run this Module.
- Thus, this code will hide 2 rows with zero values inside the dataset.
Read More: How to Hide Zero Values in Excel Pie Chart (3 Simple Methods)
3. Hiding Rows Only When All Values Are Zero
For the last method, we have changed our dataset a bit. In this case, we will only hide the rows that have 3 zero values. This means we will hide rows 6 and 9. Here, our code will have “Nested For Next Loop”.
Steps:
- Firstly, as shown in the first method, bring up the Module window.
- Secondly, type the following code inside that.
Option Explicit
Sub Hide_Rows_Zero_Two_Loops()
Dim x1 As Long
Dim x2 As Long
Dim qq As Boolean
'loop all the rows that has data
For x1 = 5 To 10
qq = True
'loop column B to D
For x2 = 2 To 4
'when higher value than 0 is found, we will not hide it
If Cells(x1, x2).Value > 0 Then
qq = False
Exit For
End If
Next x2
Rows(x1).Hidden = qq
Next x1
End Sub
VBA Code Breakdown
- First, we are calling our Sub procedure Hide_Rows_Zero_Two_Loops.
- Then, we declare the variable types.
- Next, we use a For Next Loop to go through each row of our dataset.
- After that, this code goes through the columns B to D using another For Next Loop.
- Then, it calculates if the cells are greater than 0.
- Thus, this code works to hide rows in Excel whenever all cells of a row are equal to zero.
- Then, as shown in method 1, we Save and Run this Module.
- Thus, this code will hide 2 rows with zero values inside the dataset.
Read More: How to Hide Zero Values in Excel Pivot Table (3 Easy Methods)
Practice Section
We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.
Conclusion
We have shown you 3 Macro to hide rows with zero values in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!
Hi,
Is there a VBA code that I can use for Hiding Rows Only When All Values Are Zero but with the option to add input box, so that each time I don’t have to go back to change the range?
Thank you.
Hi Medha, Thanks for reaching out. Please try this code below. Hope this is the solution to your problem.