Excel - automatically hiding select rows based on drop down selection

Copper Contributor
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 and my dashboard does not change at all. However, when I key in something into a random cell, the page reloads and rows 57:72 become hidden. But then, it stays the same across all my selections and I cannot unhide it again through the drop box selection. May I know if anyone has an alternative suggestion? Thank you in advance!
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row = 3 And Target.Value = "1"
Then Application.Rows("57:72").Select Application.Selection.EntireRow.Hidden = False
Else Application.Rows("57:72").Select Application.Selection.EntireRow.Hidden = True
End If
End Sub
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 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.

@Haytham Amairah 

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 

@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

@Haytham Amairah 

 

Thank you very much for the code to set the print area.  It works great and I really appreciate your help.

maybe the other way round...
ALT + F11 and click on the desired picture, copy the code ... change the lines as desired beforehand.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Bereich As Range, Berechnung As Long
Berechnung = Application.Calculation
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
Me.Unprotect ("1234")
Set Bereich = Intersect(Target, Range("A15:A1009"))
If Not Bereich Is Nothing Then
Application.EnableEvents = False
Range("A15:A1009").EntireRow.Hidden = False
Range("A10:A1000").EntireRow.AutoFit
Range("A" & 45 + WorksheetFunction.CountA( _
Range("A15:A1009")) & ":A1009").EntireRow.Hidden = True
End If
Me.Protect ("1234")
Application.EnableEvents = True
Application.Calculation = Berechnung
Application.ScreenUpdating = True
End Sub

Regards,
Nikolino
(Knows I don't know anything)
desired picture = disired sheet

@Haytham Amairah 

 

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!

@RishiSumit 

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,

@RishiSumit This is exactly what I am trying to do.  Did you find a resolution? I originally created this structure:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Select Hyper-V
If Range("B26") = "Hyper-V" Then
Rows("28:29").EntireRow.Hidden = False
Else
Rows("27").EntireRow.Hidden = True
End If

'Select VMware
If Range("B26") = "VMware" Then
Rows("27").EntireRow.Hidden = False
Else
Rows("28:29").EntireRow.Hidden = True
End If
End Sub

 

It worked once or twice then it stopped working. Now what is happening is I can choose VMware from the dropdown and it shows and hides the correct lines. However, when I change the field to Hyper-V, it does not remove the VMware rows and replace them with the Hyper-V rows.

 

Thanks,

Jerry

@Jerry_Reddick Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Range("B26")

Case "Hyper-V"
    Rows("28:29").EntireRow.Hidden = False
    Rows("27").EntireRow.Hidden = True

Case "VMware"
    Rows("27").EntireRow.Hidden = False
    Rows("28:29").EntireRow.Hidden = True

End Select

End Sub

 

Thanks @Riny_van_Eekelen and @Jerry_Reddick  for your responses.

 

Yes, for me, I am able to hide specific rows for my worksheet.


This is the code I am using :

 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("C14") = "Residential refinance" Then
        Rows("18:19").EntireRow.Hidden = False
    Else
        Rows("18:19").EntireRow.Hidden = True
    End If
   
    If Range("C21") = "Medico" Then
        Rows("44:45").EntireRow.Hidden = True
    Else
        Rows("44:45").EntireRow.Hidden = False
       
    End If
   
        If Range("C32") = "Yes" Then
        Rows("34:37").EntireRow.Hidden = False
    Else
        Rows("34:37").EntireRow.Hidden = True
    End If
    End Sub

 

__________________________________________

However, until now I wasnt able to hide the rows for each of the two options from Cell C21 dropdown - only one.

 

When I select the other option, both follow-up questions remain visible (rows 42:43) & rows (44:45). I will now try @Riny_van_Eekelen 's code because it seems like that might allow me to only keep one specific question (either rows 42:43 or rowas44:45) visible depending on the dropdown option selected from cell C21.

 

Other than that, my code has been running properly so far (key word: So far).  Lets see how it behaves once I share the Excelsheet with my end-users. (coz that's usually when it begins to act up usually) 

 

Thank you guys!