# VBA Code to Automatically Hide/Unhide Rows based on Formula Display Value of 0



## Etak28 (Yesterday at 2:39 PM)

Hello all!

Brand new to this forum and to VBA code so please bear with me.

I've searched and tried code from various threads on here and none of them have worked for me even though this topic has been discussed quite a few times.

What I'm trying to do is automatically hide rows 10:27 on my Sheet 1 if the displayed formula value in cells C10:C27 = 0. 
If the displayed formula value is anything greater than 0 in cells C10:C27, then I'd like those rows to be automatically displayed.

For some background, cells C10:C27 use a formula to automatically pull data from 1 of 5 sheets depending on which one is used. Not sure if it's useful, but the cell that initiates that change on every sheet is Cell B6.

Please let me know if any other pertinent information is needed.
Appreciate any and all help!


----------



## rpaulson (Yesterday at 2:51 PM)

try this

```
Sub Hide_row()
For r = 10 To 27
    If Cells(r, "C").Value = 0 Then Rows(r).Hidden = True Else Rows(r).Hidden = False
Next r
End Sub
```


----------



## DanteAmor (Yesterday at 2:53 PM)

Hi and weclome to MrExcel!

I suppose you have a formula i*n each cell, in C10, in C11 up to cell C27*, if it is correct and you modify some value in another of the sheets, then automatically the result of the formulas in C10 to C27 will be updated.
If the above is correct, then put the following code *in the events of your sheet*:


```
Private Sub Worksheet_Calculate()
  Application.Volatile
  Dim i As Long
  For i = 10 To 27
    Rows(i).Hidden = Range("C" & i).Value = 0
  Next
End Sub
```

*SHEET EVENT*
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

*NOTE*: I'm not sure if it will work on MacOs, I have no way to test, but some code works fine on Windows and MacOs.


----------



## Etak28 (Yesterday at 3:06 PM)

rpaulson said:


> try this
> 
> ```
> Sub Hide_row()
> ...


This works, however, it isn't automatic when the values change. The Macro needs to be run manually each time the value is known to change.

Ideally, every time the values in those cells changes from 0 to something > 0, the sheet automatically displays those lines.


----------



## Etak28 (Yesterday at 3:07 PM)

DanteAmor said:


> Hi and weclome to MrExcel!
> 
> I suppose you have a formula i*n each cell, in C10, in C11 up to cell C27*, if it is correct and you modify some value in another of the sheets, then automatically the result of the formulas in C10 to C27 will be updated.
> If the above is correct, then put the following code *in the events of your sheet*:
> ...


Thanks for the welcome!

So I'm not sure if this is due to MacOS or the complexity of my formulas, but every time data is entered on another sheet to cause the change, the workbook freezes.


----------



## DanteAmor (Yesterday at 3:31 PM)

Etak28 said:


> So I'm not sure if this is due to MacOS or the complexity of my formulas, but every time data is entered on another sheet to cause the change, the workbook freezes.


The problem is the macro, since it is executed by each of the formulas.
Another option is to check when you activate the sheet. That is, you can go to the other sheets to make changes so that the formulas of the sheet in question are updated, then when you activate the sheet in question, the results of the formulas in column C will be reviewed.
Try the following code.


```
Private Sub Worksheet_Activate()
  Dim i As Long
  For i = 10 To 27
    Rows(i).Hidden = Range("C" & i).Value = 0
  Next
End Sub
```


----------



## Etak28 (Yesterday at 3:46 PM)

DanteAmor said:


> The problem is the macro, since it is executed by each of the formulas.
> Another option is to check when you activate the sheet. That is, you can go to the other sheets to make changes so that the formulas of the sheet in question are updated, then when you activate the sheet in question, the results of the formulas in column C will be reviewed.
> Try the following code.
> 
> ...


Oh man, this is it! Thanks so much!

One quick addition. Is it simple enough to add a line of code to have all line items unhidden to start? Then as a sheet is activated that's when the macro begins and if that sheet went back to unactivated so to speak all rows show again even if 0.


----------



## DanteAmor (Yesterday at 7:00 PM)

Replace the code with the following:


```
Private Sub Worksheet_Activate()
  Dim i As Long
  For i = 10 To 27
    Rows(i).Hidden = Range("C" & i).Value = 0
  Next
End Sub

Private Sub Worksheet_Deactivate()
  Rows("10:27").Hidden = False
End Sub
```


----------



## Etak28 (Yesterday at 7:58 PM)

DanteAmor said:


> Replace the code with the following:
> 
> 
> ```
> ...


I think this is where things might get a little more complicated, and I also may not have supplied you with enough information.
This new code is working, but only flashes all of the lines for a bit when I go back to the first sheet. Since everything is zeroed out it hides it all just as it did with the previous code you gave me.

-I'd like the default to be that lines 10:27 always show (no matter if 0 or not).
-Then if the values of C10:C27 populate with any values > 0, I'd like the 0 rows to automatically hide.
-Finally, if the values of lines 10:27 all go back to 0, I'd like them all to show again since we're back to the first, default step.

Hope that makes sense? Everything else with the base code is perfect, I'd just like my team to be able to see all the rows as a default.


----------



## DanteAmor (Yesterday at 8:31 PM)

I hope I have understood. Delete everything and try this:


```
Private Sub Worksheet_Activate()
  Dim i As Long, n As Long
  Dim rng As Range
  
  Application.ScreenUpdating = False
  
  Set rng = Range("C10:C27")
  n = WorksheetFunction.CountIf(rng, 0)
  If n = rng.Count Then
    rng.Rows.Hidden = False
  Else
    For i = 10 To 27
      Rows(i).Hidden = Range("C" & i).Value = 0
    Next
  End If
  
  Application.ScreenUpdating = True
End Sub
```


----------



## Etak28 (Yesterday at 2:39 PM)

Hello all!

Brand new to this forum and to VBA code so please bear with me.

I've searched and tried code from various threads on here and none of them have worked for me even though this topic has been discussed quite a few times.

What I'm trying to do is automatically hide rows 10:27 on my Sheet 1 if the displayed formula value in cells C10:C27 = 0. 
If the displayed formula value is anything greater than 0 in cells C10:C27, then I'd like those rows to be automatically displayed.

For some background, cells C10:C27 use a formula to automatically pull data from 1 of 5 sheets depending on which one is used. Not sure if it's useful, but the cell that initiates that change on every sheet is Cell B6.

Please let me know if any other pertinent information is needed.
Appreciate any and all help!


----------



## Etak28 (Yesterday at 8:41 PM)

DanteAmor said:


> I hope I have understood. Delete everything and try this:
> 
> 
> ```
> ...


That's it!!
Thank you so much for your help today, I really appreciate it!


----------

