How to Hide Rows with Zero Values in Excel Using Macro (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Hide Rows with Zero Values in Excel Using Macro: 3 Suitable Macros

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.

Macro to Hide Rows with Zero Values in Excel 1

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.

 - Turning On Developer Tab


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.

Steps:

  • 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.

 - Developer Tab

  • 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.

 - Insert Module

  • 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

Macro to Hide Rows with Zero Values in Excel 6

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.
  • Afterward, Save the Module.
  • Then, put the cursor inside the Sub procedure and press Run.

Run Code

  • 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.

Macro to Hide Rows with Zero Values in Excel 2

  • By doing so, the code has hidden rows 6 and 8.

Dataset

Read More: VBA to Hide Rows Based on Cell Value in Excel


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:

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

Macro to Hide Rows with Zero Values in Excel

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.

Sample Dataset 2


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”.

Sample Dataset 3

Steps:

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

Macro to Hide Rows with Zero Values in Excel 3

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.

Macro to Hide Rows with Zero Values in Excel 4

Read More: VBA to Hide Rows Based on Criteria in Excel


Practice Section

We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.

Macro to Hide Rows with Zero Values in Excel 5


Download Practice Workbook


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!


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

2 Comments
  1. 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.

      Option Explicit
      Sub Hide_Rows_Zero_Two_Loops()
      Dim x1 As Long
      Dim x2 As Long
      Dim qq As Boolean
      Dim cRange As Range
      'loop all the rows that has data
       Set cRange = Application.InputBox("Specify the Cell Range", _
          "ExcelDemy", Type:=8)
      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
      

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo