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

thelad

Board Regular
Joined
Jan 28, 2011
Messages
235
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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,457
Office Version
365
Platform
Windows
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:
Code:
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

Board Regular
Joined
Jan 28, 2011
Messages
235
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,457
Office Version
365
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,573
Messages
5,469,473
Members
406,655
Latest member
pwilson06

This Week's Hot Topics

Top