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.

**Table of Contents**Expand

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

- 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:** 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
```

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

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

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

- How to Hide the Same Rows Across Multiple Excel Worksheets
- VBA to Hide Rows Based on Criteria in Excel
- How to Hide Blank Rows in Excel VBA
- VBA to Hide Rows in Excel

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

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.3hides 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!