autohide and auto-unhide rows based on a zero value in column a

[mikeb7500]

I have a spreadsheet that I would like to AUTO-HIDE, AND AUTO-UN-HIDE the row based if the Column 1 value in that Row s 0. I have 20 Rows dispersed throughout 100 rows I would like to do this with, so that A1=1, Row A will auto=unhide, but if I have A1=0(or not answered yet), Row A will be hidden.

This is for a question where I ask:

Do you have any children? If the answer is YES, I expose the next few Rows that ask questions about the children, like name etc because the value in Column one is 1.

But if the answer is NO (or, not answered yet), those Rows remain hidden. (YES=1, NO=0) No children, no information needed

So the first cell in each row will have a 0 or a 1 in it.

I felt this one the easiest way to do it, since I have several questions, that the Rows that follow each question will either remain hidden, or unhide when the value is 1 so I can ask the follow-up questions.

Thanks for your help

 

[Trebor76]

Yes, you need to unprotect the sheet to hide rows from it. With no password this will do the job:

Option Explicit
Private Sub Worksheet_Calculate()

    ActiveSheet.Unprotect

    With Application
        .ScreenUpdating = False 'Turn screen updating off
        .EnableEvents = False 'Turn macros off (recommended for worksheet_event macros as it stops the code getting stuck in a loop)
    End With

    'If the calculated value in cell K9 is zero, then...
    If Val(Range("K9")) = 0 Then
        '...hide rows 10 to 14 (inclusive)
        Rows("10:14").Hidden = True
    'Else, if the calculated value in cell K9 is one, then...
    ElseIf Val(Range("K9")) = 1 Then
        '...unhide rows 10 to 14 (inclusive)
        Rows("10:14").Hidden = False
    End If
    
    With Application
        .ScreenUpdating = True 'Turn screen updating on
        .EnableEvents = True 'Turn macros on
    End With
    
    ActiveSheet.Protect

End Sub

I can see there may be other issues though if this tab is to collect data but it's protected?

HTH

Robert

 

[mikeb7500]

Thanks Robert, I added these lines and it works perfectly!

Code:
ActiveSheet.Unprotect

ActiveSheet.Protect

 

[Trebor76]

Thanks for letting us know and you're welcome.