2010VBA AutoHide(&unhide) rows based on dynamic cell value

Hello,

New member and first time post. Intermediate Excel user, complete VBA novice.

Goal:

I have a workbook that has several sheets. In sheet1, there are inputs that determine whether Column A in other Sheets has a 0 or 1 in it. I need a VBA code that will autohide the each row that Column A has a 0 in. Autofilter would work fine except this workbook will be used frequently and the cells in column A will change from 1 to 0 and back, and I can't get Autofilter to be dynamic as needed. (once hidden, if the hidden cell changes back to a 1, it doesn't automatically unhide). Can I get the VBA code to work for the entire workbook? Or would I need to insert it into each Sheet's respective VBA input?

Other Details:

Column A isn't very long with 80 cells or so being referenced (usually no more than 30 rows would need to be hidden).
Sheets2,3,n...'s Column A is based on inputs from Sheet1 which is formula driven (if that matters)

I have attached a work-in-progress workbook that this code will be used in. All data is ficticious. 3.0 with VBA.xlsm

Thank you very much for your time. I have searched several forums and have tried to copy/paste several different codes, but have not had any success. (some of the code froze my workbook altogether).

I will be monitoring this thread like a hawk, if you need any additional detail, just ask.

Thanks again,
Shawn

Bump, no response.

Hi,

This can be approached in a number of different ways based on need. What event would would you like to trigger the macro to run? Do you want it to change immediately as the 1 changes to a 0? If so, since Column A is a formula, what causes it to change in value?

Thank you BigBas,

The event to trigger would be anytime there is a change in those cells. I had code that was close to my needs, but updated anytime an input changed in the specific sheet, it would stutter and delay/stall (very inconvenient). The input in the workbook i have attached to this thread is based on life expectancy cells which feeds a formula to know that if that client is still alive for that respective year (row), show a one, and if the clients are both deceased (the plan ends), show a zero. All of this occurs on sheet1, but shows up on several other sheets. So to answer your question, the macro would need to update as the input changes (on sheet1) on the fly. This is why it needs to be able to unhide as well as hide.

I hope that offers you enough information.

Thanks.

Enter the following code in the Worksheet module of the Input sheet. To open this module, in Excel, right click on the Input tab, and select view code. Enter the following code. Let me know if it works properly.

Private Sub Worksheet_Calculate()
    Dim ws As Worksheet
    Dim fnd As Range
    Dim lRow As Long
    
    Const fndVal As String = "AutoHide"
    
    Application.ScreenUpdating = False
    
    
    For Each ws In Worksheets
        If ws.Name <> "Input" Then
            On Error Resume Next
                Set fnd = ws.Cells.Find(fndVal)
            On Error GoTo 0
            
            If Not fnd Is Nothing Then
                ws.Rows.Hidden = False
                lRow = ws.Cells(Rows.Count, fnd.Column).End(xlUp).Row
                
                For i = lRow To fnd.Row Step -1
                    If ws.Cells(i, fnd.Column) = 0 Then
                        ws.Rows(i).Hidden = True
                    End If
                Next i
            End If
        End If
    Next ws
    
    Application.ScreenUpdating = True

End Sub

Works Perfectly!!!

Thanks so much BigBas. Very much appreciated.

You're welcome