How to Automatically Hide Rows with Zero Values in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to automatically hide rows with zero values in Excel. In Microsoft Excel, we store different types of data and use them according to our needs. Sometimes, the dataset may contain 0 in some cells. In those cases, we may need to hide the entire row. Today, we will demonstrate 2 easy ways. Using these methods, you can easily hide rows with values in Excel. So, without further delay, let’s start the discussion.


How to Automatically Hide Rows with Zero Values in Excel: 2 Easy Ways

To explain the methods, we will use a dataset that contains information about the sales amount of some sellers for the first two months. Some sellers had 0 sales in some months. From the dataset, we can say that we need to automatically hide Rows 7,10,12, and 14.


1. Apply Excel VBA to Automatically Hide Rows with Zero Values

The easiest way to automatically hide rows with zero values is to use Excel VBA. We can use VBA depending on our purposes. Sometimes, you may need to hide rows if only one cell contains 0 and sometimes if all cells contain 0. Using VBA, you can complete the task in various ways. Here, we will show 3 different Macro codes that serve our purpose.


1.1 With InputBox

In the first method, we will insert a macro input box to select the range and automatically hide rows with zeros. This code will hide rows if any one cell contains zero. So, let’s follow the steps below to see how we can implement the technique.

STEPS:

  • First of all, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.
  • Alternatively, you can press the Alt + F11 key to open the Visual Basic window.

Apply Excel VBA to Automatically Hide Rows with Zero Values

  • Secondly, click on the Insert tab and select Module to open the Module window.

Apply Excel VBA to Automatically Hide Rows with Zero Values

  • In the Module window, type the code below:
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

Apply Excel VBA to Automatically Hide Rows with Zero Values

This code will hide the entire row if it finds any cell with zero value. Firstly, it opens an input box where we need to insert the range of the dataset. Here, we have declared two variables. The first one is cRange and the second one is qq. The cRange is the range that we insert in the input box. Similarly, qq is the cells of that range. In the For Next Loop, it checks if the cell value equals 0 or not. If it is equal to 0, then the code hides the entire row.

  • Now, press Ctrl + S to save the code.
  • Then, press the F5 key to run the code.
  • Otherwise, go to the Developer tab and select Macros. It will open the Macro window.

Apply Excel VBA to Automatically Hide Rows with Zero Values

  • In the Macro window, select the code and Run it.

Apply Excel VBA to Automatically Hide Rows with Zero Values

  • As a result, the input box will appear.

Apply Excel VBA to Automatically Hide Rows with Zero Values

  • In the following step, select the range where you want to apply the VBA code.
  • Click OK to proceed.

Apply Excel VBA to Automatically Hide Rows with Zero Values

  • Finally, you will be able to automatically hide rows with zero values.

Apply Excel VBA to Automatically Hide Rows with Zero Values

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


1.2 Without InputBox

This process is similar to the previous one. But it has one difference. Here, we will not use the input box. We will use the range directly inside the code. So, let’s pay attention to the steps below to learn more.

STEPS:

  • Firstly, open the Visual Basic window. You can do it by selecting the Visual Basic option from the Developer tab.
  • Alternatively, you can press the Alt + F11 key to open it.
  • After that, select Insert >> Module in the Visual Basic window. It will open the Module window.
  • Now, type the code below in the Module window:
Option Explicit
Sub Hide_Rows_with_Zeros()
    Dim qq As Range
    For Each qq In Range("B6:D14")
        If Not IsEmpty(qq) Then
            If qq.Value = 0 Then
                qq.EntireRow.Hidden = True
            End If
        End If
    Next
End Sub

Apply Excel VBA to Automatically Hide Rows with Zero Values

In this code, you need to change the range according to your needs. In our case, the dataset range is B6:D14. Here, you need to insert the range on which you want to apply the code.

  • Press Ctrl + S to save the code.
  • In the next step, press the F5 key to run the code.
  • If you have two or more modules, then, the Macros window will appear.
  • Select the desired code from there and click on Run.

Apply Excel VBA to Automatically Hide Rows with Zero Values

  • Instantly, the rows with zero values will be hidden.

Apply Excel VBA to Automatically Hide Rows with Zero Values

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


1.3 Only When All Values Are Zero

In this method, we will hide rows automatically only when all values of the row are zero. In the dataset below, you can see Rows 10 and 14 contain 0 in all cells. So, our target is to hide these two rows only. Also, you can spot zero values in Rows 7 and 12. But we will not hide them, as they only contain a single zero value.

Apply Excel VBA to Automatically Hide Rows with Zero Values

Let’s observe the steps below to learn more about the method.

STEPS:

  • In the first place, you need to open the Visual Basic window.
  • To do so, go to the Developer tab and select Visual Basic.
  • Alternatively, you can press the Alt + F11 key to open it.
  • Secondly, select Insert >> Module in the Visual Basic window. It will open the Module window.
  • Now, type the code below in the Module window:
Option Explicit
Sub Hide_Rows_Zero_Two_Loops()
Dim row As Long
Dim col As Long
Dim qq As Boolean
For row = 6 To 14
    qq = True
    For col = 2 To 4
        If Cells(row, col).Value > 0 Then
            qq = False
            Exit For
        End If
    Next col
    Rows(row).Hidden = qq
Next row
End Sub

Apply Excel VBA to Automatically Hide Rows with Zero Values

Here, we have used two For Next Loop. The first loop goes through row-wise and the second one goes through column-wise. Then, it checks the cells greater than 0. If all values of a row are 0 then, it hides the entire row. Otherwise, it shows it in the dataset. In this way, the code works. In our case, we applied the from Rows 6 to 14 and from Columns B to D. You need to change these values inside the code when you apply it to our dataset.

  • Press Ctrl + S to save the code.
  • Then, hit the F5 key to run the code.
  • Otherwise, you can go to the Developer tab and select Macros from there.
  • In the Macros window, select the desired code and Run it.

Apply Excel VBA to Automatically Hide Rows with Zero Values

  • Finally, the code will automatically hide rows that contain only zero values in all cells.

Apply Excel VBA to Automatically Hide Rows with Zero Values

Read More: Hide Rows and Columns in Excel


2. Automatically Hide Rows with Zero Values Using Excel Formula

In the last method, we will use a formula and the Filter feature of Excel to hide rows with zero values. To build the formula, we will use the combination of the IF function and the COUNTIF function. But this method has two drawbacks. You can only hide rows using this method if a specific range of cells contains 0. You can not hide rows automatically if the zero value stays in other cells. Also, this process is not dynamic. That is why you need to reapply the filter if anything changes. In the dataset below, we can see Rows 7, 10, and 14 contain zero values.

Automatically Hide Rows with Zero Values Using Excel Formula

Let’s follow the steps below to see how we can apply the method easily.

STEPS:

  • In the beginning, we need to add a helper column to insert the formula.
  • Here, the Filter column is the helper column.

Automatically Hide Rows with Zero Values Using Excel Formula

  • Secondly, select Cell E6 and type the formula below:
=IF(COUNTIF(C6:D6,0)=2,"Hide","Show")
  • Hit Enter to see the result.

This formula checks if the number of non-zero cells of the range C6:D6 is 2. For that purpose, we have used the COUNTIF function. If the number of the non-zero cells is greater than 0, then the formula will store SHOW in Cell E5. On the other hand, if the number of zero cells is equal to 2, then it will store Hide in Cell E5. To implement the conditions correctly, we have used the IF function.

  • Thirdly, drag down the Fill Handle to copy the formula.

  • After that, navigate to the Home tab.
  • Then, click on the Sort & Filter option and select Filter from the drop-down menu.

  • In the following step, click on the wide drop-down arrow of the Filter header.

  • At this moment, uncheck Hide from the menu and click OK to move forward.

  • As a result, you will be able to automatically hide rows with zeros.

  • Finally, hide the helper column to make the dataset look clean.

Read More: Excel Hide Rows Based on Cell Value with Conditional Formatting


Download Practice Workbook

You can download the practice book from here.


Conclusion

In this article, we have 2 easy methods to automatically hide rows with zero values in Excel. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles


<< Go Back to Hide Rows | Rows in Excel | Learn Excel

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.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

2 Comments
  1. This hides rows that has negative numbers how do I hide rows that are truly zero? Meaning only rows that do not have a negative or positive number.

    • Hello Frank,
      This article contains different methods for different situations. Method 1.3 hides the numbers that are less than Zero. But, if you want to hide zeros only then you can try the following code.

      Option Explicit
      Sub Hide_Rows_With_Zero()
      Dim row As Long
      Dim col As Long
      Dim qq As Boolean
      For row = 6 To 14
      qq = True
      For col = 2 To 4
      If Cells(row, col).Value <> 0 Then
      qq = False
      Exit For
      End If
      Next col
      Rows(row).Hidden = qq
      Next row
      End Sub

      I hope this will help you to solve your problem. Please let us know if you have other queries.
      Thanks!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo