Survey - Hide rows if N is selected Unhide Rows if Yes is selected

[thelad]

Hi,

I am completing a survey on excel in which I ask a number of questions. I would like to unhide and leave rows hidden based on User selecting Yes or No

If No is selected from dropdown list (Yes or No Can be Selected) in row 13 it leaves row 14 to 24 hidden, but if Yes is Selected it will unhide the rows 14 to 24. To note there will be numerous columns that can be yes or no.

For Example D13 to AB13 might have yes or no to be selected. One occurrence of Yes will cause the rows to unhide.

YES/NO selection will start from D13 but can go to H13 or itcould go to AB13...so the test to see if Yes /No is selected needs to be dynamic.

Any help Appreciated.

 

[Joe4]

Right-click on the sheet tab name at the bottom of your sheet, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    
'   Enter range to consider
    Set rng = Range("D13:AB13")
    
'   Run macro if a cell in the above range is changed
    If Not Intersect(Target, rng) Is Nothing Then
'       If any cells in rng is "Yes", unhide rows
        If Application.WorksheetFunction.CountIf(rng, "Yes") > 0 Then
            Rows("14:24").EntireRow.Hidden = False
        Else
            Rows("14:24").EntireRow.Hidden = True
        End If
    End If
    
End Sub

Then, anytime a value is updated in the range D13:AB13, if there are any "Yes" values in that range, rows 14:24 will be visible, else they will be hidden.

 

[thelad]

Hi Joe,

Thanks for that. Can the D13 to AB13 b dynamic In any way?

AB13 might not be last column, t could go out Further for user to select yes/No

 

[Joe4]

Why not just make it the last possible column you might ever use (if it could be any, then try "XFD13")?
Would there be any danger in doing that?