Dynamic Rows Hide/Unhide through VBA on the basis of value selected from dropdown

[Yash9154]

Hi,

I want a dynamic solution for below requirements:

Requirement: if a user select any value from the dropdown then it should make visible only those row which contains the same value in column A from user based selected dropdown value. and if user select "All" from drop down then it should show all tables/scenarios.

Also it will be good if drop down should be multiselect.

>>For Example: if user select "Cash" from dropdown then the code should process from row 5 including header and should make only visible Row5 to Row13.

Note: Row1 to Row4 shouldn't get effected by this process.

Also attached file for your references.

Thank you in advance :)

[#2 · Accepted solution]

@Yash9154 

In the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s As String
    Dim c As Range
    Dim v As Range
    If Not Intersect(Range("B2"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Cells.EntireRow.Hidden = False
        s = Range("B2").Value
        Select Case s
            Case "All", ""
                ' We're done
            Case Else
                For Each c In Range("A5:A37")
                    If c.Value <> s And c.Offset(0, 1).Value <> s Then
                        If v Is Nothing Then
                            Set v = c
                        Else
                            Set v = Union(v, c)
                        End If
                    End If
                Next c
                If Not v Is Nothing Then
                    v.EntireRow.Hidden = True
                End If
        End Select
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

[#3]

@Yash9154 

I forgot to mention: insert the following line at the top of the module:

Option Compare Text

[#4]

@Hans Vogelaar, thanks for your quick response :)

One more question, is it possible to make is multiselect as well like if user wants to see more than one selection.?

[#5]

@Yash9154 

See the attached version.

[#6]

ok i saw this file but can't we do this multi selection thing by dropdown with checkbox thing?

[#7]

@Yash9154 

It's not easy to please you! The answer is no, since Excel does not provide such a dropdown.

A clunky workaround is a multi-select list box with check boxes.

See the attached version. There is no "ALL" option here.