Hide row range when two dropdowns values are selected

[Nick70]

Hi,

I have a sheet with two dropdowns one in cell B5 and one in cell B10.

Dropdown options are 'Yes' and 'No'.

I would like to have a macro so that if both dropdowns are showing 'Yes' then rows (20:30) are hidden (any other dropdown combination and rows(20:30) must be visible).

I need a VBA code not a filter as this is part of larger exercise (and filter would not work).

How do I do that?

Thanks,
N.

 

[FormR]

Hi, you could try:

Rows("20:30").Hidden = (Range("B5").Value = "Yes" And Range("B10").Value = "Yes")

 

[Nick70]

That works well.

Thanks,
N.

 

[bebo021999]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Union(Range("B5"), Range("B10"))) Is Nothing Then Exit Sub
Range("A20:A30").EntireRow.Hidden = False
    Select Case LCase(Range("B5") & Range("B10"))
        Case Is = "yesyes"
            Range("A20:A30").EntireRow.Hidden = True
        Case Else
            Range("A20:A30").EntireRow.Hidden = False
    End Select
End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Hide rows based on two dropdown selections - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Nick70]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Union(Range("B5"), Range("B10"))) Is Nothing Then Exit Sub
Range("A20:A30").EntireRow.Hidden = False
    Select Case LCase(Range("B5") & Range("B10"))
        Case Is = "yesyes"
            Range("A20:A30").EntireRow.Hidden = True
        Case Else
            Range("A20:A30").EntireRow.Hidden = False
    End Select
End Sub

Thanks!