Forum Discussion
Excel - automatically hiding select rows based on drop down selection
Hi there! I have a drop down selection of 10 items on my dashboard tab and am trying to only show rows 57 to 72 when I select one of them. I am using the following code but somehow it is not working...
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
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.
