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

## 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)**