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

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.

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: How to Hide Zero Data Labels in Excel Chart (4 Easy Ways)


Similar Readings


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

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

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

Macro to Hide Rows with Zero Values in Excel 5


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

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo