Forum Discussion
Excel - automatically hiding select rows based on drop down selection
Hi,
If I understand you well, you want to show rows 57 to 72 when you select the value of 1 from the dropdown list, otherwise, you want to hide them.
I think that the dropdown list in cell B3?
Based on that, please try this code instead:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B3") = "1" Then
Rows("57:72").EntireRow.Hidden = False
Else
Rows("57:72").EntireRow.Hidden = True
End If
End Sub
I am looking to create a worksheet where a user is presented with different options (more than 2 options) from a dropdown and based on their responses (i.e if user chooses any one of 2 options - eg. choice A or choice B), a new question appears on the Excelsheet in a row below the first question, otherwise they don't.
And then based on the users' response to this second question (posed as a dropdown choice), a third question appears or does not appear.
As is obvious, I am seeking to have several nested questions within the Excelsheet where each subsequent question shows up as a Dropdown based on the user's response from a previous question (also a dropdown).
I am able to create dropdowns for each question and create one of the questions to appear based on a prior question, but am having trouble going any further.
Any help would be much appreciated. Thank you!
Perhaps you mean nested drop-down list. See, for example, https://www.youtube.com/watch?v=7mo4COng7Sg
- Thanks Sergei.
This video shows how to draw data from a dropdown list that is visible on the Excel sheet regardless of the user's response.
However, I am trying to make a sheet where a follow up question (& its associated dropdown list) does not become visible unless user chooses either say option C or D (out of 6 options a,b,c,d,e,f). Once user chooses option c or d --> then the next question (& its dropdown list) appears in the following row.
Thank you,
Thank you for the info on how to use a macro to hide rows. I have a question as an extension to the first question.
I see how you hide rows based on the content of a cell. Is it also possible to use the same macro to set the print area of the document so that it prints the rows that are still active?
Thanks,
wmsawchuk
Yes, that's possible!
Try this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B3") = "1" Then
Rows("10:15").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$15"
Else
Rows("10:15").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$9"
End If
End SubThank you very much for the code to set the print area. It works great and I really appreciate your help.
Thanks Haytham, unfortunately it seems like it is my excel sheet that is causing the problem. The same issue remains (that the bars don't get hidden until I type something into cell J3, after which it does not unhide again). I tried your method on a new excel file and it worked. If you happen to know how I can troubleshoot this issue please let me know. Thank you!
Hi,
Please provide me with screenshots of your dashboard to figure out what the problem is.
It's best if you can attach the workbook.
