Method 1 – Applying Macro InputBox to Hide Rows with Zeros in Excel
- Bring up the VBA Module window, where we type our codes.
- From the Developer tab → select Visual Basic. Alternatively, you can press ALT+F11 to bring up this too.
- The VBA window will pop up.
- From the Insert tab, select Module.
- Insert VBA code to hide rows with zero values.
- 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
- We are calling our Sub procedure Hide_Rows_Zero_InputBox.
- Declare the variable types.
- 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.
- Use a For Next Loop to go through each cell of our selected range.
- If the value of a cell is different than the previous cell, we hide rows with zero values.
- This code works.
- Our code will execute and ask for the cell range to hide rows with zero values.
- Select the cell range D5:D10.
- Press OK.
- The code has hidden rows 6 and 8.
Method 2 – Using VBA Macro Without InputBox to Hide Rows with Zero Values
Steps:
- As shown in the first method, bring up the Module window.
- 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
- We are calling our Sub procedure Hide_Rows_Zero_2.
- We declare the variable types.
- Use a For Next Loop to go through each cell of our selected range, D5:D10.
- If the value of a cell is “0”, it hides the EntireRow.
- This code works to hide rows in Excel whenever zero values appear.
- As shown in method 1, we Save and Run this Module.
- This code will hide 2 rows with zero values inside the dataset.
Method 3 – Hiding Rows Only When All Values Are Zero
Steps:
- As shown in the first method, bring up the Module window.
- 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
- We are calling our Sub procedure Hide_Rows_Zero_Two_Loops.
- Declare the variable types.
- Use a For Next Loop to go through each row of our dataset.
- This code goes through the columns B to D using another For Next Loop.
- It calculates if the cells are greater than 0.
- This code works to hide rows in Excel whenever all cells of a row are equal to zero.
- As shown in method 1, we Save and Run this Module.
- This code will hide 2 rows with zero values inside the dataset.
Download Practice Workbook
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.