Mursalin Ibne Salehin
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.
Download Practice Book
You can download the practice book from here.
2 Easy Ways to Automatically Hide Rows with Zero Values in Excel
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.
- Secondly, click on the Insert tab and select Module to open the Module window.
- 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
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.
- In the Macro window, select the code and Run it.
- As a result, the input box will appear.
- In the following step, select the range where you want to apply the VBAÂ code.
- Click OK to proceed.
- Finally, you will be able to automatically hide rows with zero values.
Read More: Hide Rows with Zero Values in Excel Using Macro (3 Ways)
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
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.
- Instantly, the rows with zero values will be hidden.
Read More: How to Leave Cell Blank If There Is No Data in Excel (5 Ways)
Similar Readings
- Excel Chart: Ignore Blank Axis Labels (with Easy Steps)
- Show Dash Instead of Zero in Excel (4 Easy Methods)
- How to Apply VLOOKUP to Return Blank Instead of 0 or NA
- Use VLOOKUP to Return Blank Instead of 0 (7 Ways)
- How to Hide Zero Values in Excel Chart (5 Easy Ways)
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 value in Rows 7 and 12. But we will not hide them, as they only contain a single zero value.
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
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.
- Finally, the code will automatically hide rows that contain only zero values in all cells.
Read More: How to Hide Zero Values in Excel Pivot Table (3 Easy Methods)
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.
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.
- 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: Exclude Zero Values with Formula in Excel (3 Easy Ways)
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. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.
Related Articles
- How to Remove Zeros in Front of a Number in Excel (6 Easy Ways)
- Hide Zero Values in Excel Pie Chart (3 Simple Methods)
- How to Use XLOOKUP to Return Blank Instead of 0
- Hide Chart Series with No Data in Excel (4 Easy Methods)
- How to Ignore Blank Cells in Excel Bar Chart (4 Easy Methods)
- Excel IFERROR Function to Return Blank Instead of 0
- Hide Zero Data Labels in Excel Chart (4 Easy Ways)
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 ExplicitSub Hide_Rows_With_Zero()Dim row As LongDim col As LongDim qq As BooleanFor row = 6 To 14qq = TrueFor col = 2 To 4If Cells(row, col).Value <> 0 Thenqq = FalseExit ForEnd IfNext colRows(row).Hidden = qqNext rowEnd SubI hope this will help you to solve your problem. Please let us know if you have other queries.
Thanks!