Hiding Rows based on multiple values

  • Hello,


    I have 2 worksheets - both have a row of questions. Sheet 1: Pre-Questionnaire, Sheet 2: Detailed Questionnaire (Sheet 2 starts off hidden)


    • Sheet 1 has a row of 11 questions.
    • I want the first two questions on that to show upon opening the questionnaire.
    • The second question has a Yes, No option, If Yes selected, I want the other 9 questions (rows to be made visible) AND Sheet 2 to also be now visible
    • Question 11 has a list of answers, if answer "C" is selected, I want questions (rows) in Sheet 2 from 69-80 to be unhidden.


    I have the code working for the last part - but I am struggling to get the other two evaluations to work. Code for part 2 I am using is this:


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Target.Address = "$C$25" Then
    3. If Target.Value = "C" Then
    4. Sheets("Detailed Questionnaire").Rows("69:89").EntireRow.Hidden = False
    5. Else
    6. Sheets("Detailed Questionnaire").Rows("69:89").EntireRow.Hidden = True
    7. End If
    8. End Sub
  • What about this sort of approach instead:


    I put a list of questions in sheet3 from A1 through A80 (assuming 80 questions), then used the target values you indicated (i.e. q1 on c25, q11 on c35).

    The "Option Compare Text" means it is case insensitive.

    On sheet activation q1 and q2 are copied to b25 and b26.

    If yes is typed in c26 then the following 9 questions are copied from c27 thorugh c35 and sheet 2 is made visible.

    if c is typed in c35 q69 through 80 are copied to a location in sheet 2.


    Just adjust the sheet references to suit (i.e. "set ws = sheet1" where sheet1 is your sheet, nb- use the code name if you can in preference to the sheetname.)


    You might be able to modify this to suit what you are doing, otherwise upload a demo if you want to stick to your current approach.

    cheers

    Justin

  • Welcome to the Forum.


    I have added Code Tags, please read the Forum Rules to understand why.


    Your code could be written like this


    Code
    1. Option Explicit ''/// force Variables to be explicitly compared
    2. Private Sub Worksheet_Change(ByVal Target As Range)
    3. If Target.Address = "$C$25" Then Sheets("Detailed Questionnaire").Rows("69:89").EntireRow.Hidden = Target.Value = "C"
    4. End Sub

    Please attach an example of your workbook to make it easier to help with your problem

  • Hi both,


    Thanks for your inputs. Attached is a demo file (i can't share the exact file due to sensitivity).


    You can see the VBA I have (in the Pre Questionnare) that hides rows in the detailed questionnaire


    What I would like it to do is as follows:


    1. At Opening the file, by default, Questions 1.3-1.11 should be hidden, also Worksheet Detailed Q to be hidden
    2. the answer to 1.11 should be blank at the start so 5.1 - 5.9 in the Detailed Questionnaire should also be hidden (somewhat what the code I have added in the Pre Q does)
    3. If answer for 1.2 is Yes, then I want 1.3-1.11 she be shown for answering AND The Detailed Q sheet to be visible now
    4. If 1.11 is C, then the Questions 5.1 - 5.9 on Detailed Q shown now


    An aside point, I have Radio buttons on my actual questionnaire, but when the rows are hidden they don't hide with the hidden rows - its weird. Any tips?

  • Hi Musicboi,


    I have rewritten a couple of lines so they work, I will be able to check back later but that is it for now.