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

[Etak28]

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]

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]

Hi and weclome to MrExcel!

I suppose you have a formula in 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]

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

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]

Hi and weclome to MrExcel!

I suppose you have a formula in 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.

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]

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]

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

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]

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]

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

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]

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