Forum Discussion

Yash9154's avatar
Yash9154
Copper Contributor
Jun 17, 2023
Solved

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

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 🙂

 

 

 

 

excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jun 17, 2023

    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
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 17, 2023

    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

Resources