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

Copper Contributor



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 :)






6 Replies
best response confirmed by Yash9154 (Copper Contributor)


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
                            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


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


Option Compare Text

@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.?


See the attached version.

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


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.