VBA to link drop down list to its relevent table

[ALAMZEB]

I have categories in drop down list
Each category has table of 7 rows and 18 column long.
Can I build a macro which bring that table front when category is selected from drop down list.

 

[YasserKhalil]

Try this code in worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$1" Then
        Dim LR As Long, Found
        Application.EnableEvents = False
            Cells.EntireRow.Hidden = False
            LR = Cells(Rows.Count, "D").End(xlUp).Row
            If Not IsEmpty(Target) Then
                Set Found = Range("D2:D" & LR).Find(Target.Value)
   
                If Found Is Nothing Then
                    Cells.EntireRow.Hidden = False
                Else
                    Rows("2:" & LR).EntireRow.Hidden = True
                    Found.Resize(6, 1).EntireRow.Hidden = False
                End If
            Else
                Cells.EntireRow.Hidden = False
            End If
        Application.EnableEvents = True
    End If
End Sub

 

[ALAMZEB]

Hi
Its works great but each table doesn’t have to be only 5 rows. If I insert rows in category this shows only first 5 rows
Thanks for help in advance

 

[YasserKhalil]

Ok then try the following modification

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$1" Then
        Dim LR As Long, Found As Range, Rng As Range
        Application.EnableEvents = False
            Cells.EntireRow.Hidden = False
            LR = Cells(Rows.Count, "D").End(xlUp).Row
            If Not IsEmpty(Target) Then
                Set Found = Range("D2:D" & LR).Find(Target.Value)
   
                If Found Is Nothing Then
                    Cells.EntireRow.Hidden = False
                Else
                    Set Rng = Range(Found.Address & ":" & Found.End(xlDown).Address)
                    Rows("2:" & LR).EntireRow.Hidden = True
                    Rng.EntireRow.Hidden = False
                End If
            Else
                Cells.EntireRow.Hidden = False
            End If
        Application.EnableEvents = True
    End If
End Sub