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.

**Table of Contents**hide

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

Before jumping on to the step-by-step guides, you need to make sure that the ** Developer** tab is turned on in the

**. If it is not turned on, then you can turn it on simply by following these steps:**

*Ribbon*- Firstly,
→*File*→*Options*tab → select*Customize Ribbon*.*Developer* - Then, press
.*OK*

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

**code to hide rows with zero values. Moreover, there will be a**

*VBA***in this**

*For Each Next Loop***. Without further ado, let us jump into the step-by-step guide.**

*Macro*- First, we bring up the
window, where we type our codes.*VBA Module* - To do this, from the
tab → select*Developer*. Alternatively, you can press*Visual Basic*to bring up this too.*ALT+F11*

- So, the
window will pop up.*VBA* - Next, from the
tab, select*Insert*.*Module* - Here, we insert
code to hide rows with zero values.*VBA*

- After that, type the following code inside the
window.*VBA Module*

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

**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
. Here, type*InputBox***8**denotes the input should be*Range type**only*. Moreover, if we select nothing, then the code will stop. - Then, we use a
to go through each cell of our selected range.*For Next Loop* - After that, if the value of a cell is different than the previous cell, we hide rows with zero values.
- Thus, this code works.

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

- By doing so, the code has hidden rows
**6**and**8**.

**Read More: ****How to Hide Zero Data Labels in Excel Chart (4 Easy Ways)**

**Similar Readings**

**How to Ignore Blank Series in Legend of Excel Chart****Excel IFERROR Function to Return Blank Instead of 0****How to Apply VLOOKUP to Return Blank Instead of 0 or NA****Remove Zeros in Front of a Number in Excel (6 Easy Ways)****How to Hide Chart Series with No Data in Excel (4 Easy Methods)**

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

- Firstly,
**as shown in the first method**, bring up thewindow.*Module* - Secondly, type the following code inside that.

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

**VBA Code Breakdown**

- First, we are calling our
*Sub procedure**Hide_Rows_Zero_2*. - Then, we declare the variable types.
- Then, we use a
to go through each cell of our selected range, which is*For Next Loop*.*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**, weand*Save*this*Run*.*Module* - Thus, this code will
**hide 2****rows**with zero values inside the dataset.

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

**Steps:**

- Firstly,
**as shown in the first method**, bring up thewindow.*Module* - Secondly, type the following code inside that.

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

**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
to go through each row of our dataset.*For Next Loop* - After that, this code goes through the columns
to*B*using another*D*.*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**, weand*Save*this*Run*.*Module* - Thus, this code will
**hide 2****rows**with zero values inside the dataset.

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

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

Hi,

Is there a VBA code that I can use for Hiding Rows Only When All Values Are Zero but with the option to add input box, so that each time I don’t have to go back to change the range?

Thank you.

Hi Medha, Thanks for reaching out. Please try this code below. Hope this is the solution to your problem.