Forum Discussion
Excel - automatically hiding select rows based on drop down selection
15 Replies
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 SubI 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
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 Sub
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.
