Skip to content

Show / Hide Rows or Columns Based on Drop-Down Selection | Pick Table From Drop-Down List

     

    In this video tutorial I demonstrate how to show/hide rows or columns or tables based on a drop-down list selection. This particular technique requires VBA code which I supply via the link above.

    Click here to download the featured file (including code).

    Video Table of Contents

    00:00 – Introduction

    00:49 – Create the data validation drop-down list

    01:45 – Use the VBA code to hide rows based on drop-down list selection

    Here’s the code for hiding rows:

    Private Sub Worksheet_Change(ByVal Target As Range)

Dim PayType As Range
Set PayType = Range("B2")

If Intersect(Target, PayType) Is Nothing Then Exit Sub
'add as many data sets as required
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range

'add as many options as you require
Dim FindHdg1 As Range
Dim FindHdg2 As Range
Dim FindHdg3 As Range

'put your headings in the brackets & add more headings if required
Set FindHdg1 = Cells.Find("Cash Payments")
Set FindHdg2 = Cells.Find("Debit Card Payments")
Set FindHdg3 = Cells.Find("Credit Card Payments")

Dim RowsToHide As Range
Set RowsToHide = Range("A5:A1048576")


'add a case for each option in your drop-down & and add more if required
    Select Case PayType
        Case Is = "All"
            Cells.EntireRow.Hidden = False
        
        Case Is = "Cash"
            Cells.EntireRow.Hidden = False
            Set Rng1 = FindHdg1.CurrentRegion
            RowsToHide.EntireRow.Hidden = True
            Rng1.EntireRow.Hidden = False
        
        Case Is = "Debit Card"
            Cells.EntireRow.Hidden = False
            Set Rng2 = FindHdg2.CurrentRegion
            RowsToHide.EntireRow.Hidden = True
            Rng2.EntireRow.Hidden = False
        
        Case Is = "Credit Card"
            Cells.EntireRow.Hidden = False
            Set Rng3 = FindHdg3.CurrentRegion
            RowsToHide.EntireRow.Hidden = True
            Rng3.EntireRow.Hidden = False
    End Select
    
End Sub

    04:55 – Use the VBA code to hide columns based on drop-down list selection

    Here’s the code for hiding columns:

    Private Sub Worksheet_Change(ByVal Target As Range)

Dim PayType As Range
Set PayType = Range("B2")

If Intersect(Target, PayType) Is Nothing Then Exit Sub
'add as many data sets as required
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range

'add as many headings as you require
Dim FindHdg1 As Range
Dim FindHdg2 As Range
Dim FindHdg3 As Range

'put your headings in the brackets
Set FindHdg1 = Cells.Find("Cash Payments")
Set FindHdg2 = Cells.Find("Debit Card Payments")
Set FindHdg3 = Cells.Find("Credit Card Payments")

Dim ColsToHide As Range
Set ColsToHide = Range("D1:XFD1")
    
    
'add a case for each option in your drop-down & and add more if required
    Select Case PayType
        Case Is = "All"
            Cells.EntireColumn.Hidden = False
        
        Case Is = "Cash"
            Cells.EntireColumn.Hidden = False
            Set Rng1 = FindHdg1.CurrentRegion
            ColsToHide.EntireColumn.Hidden = True
            Rng1.EntireColumn.Hidden = False
        
        Case Is = "Debit Card"
            Cells.EntireRow.Hidden = False
            Set Rng2 = FindHdg2.CurrentRegion
            ColsToHide.EntireColumn.Hidden = True
            Rng2.EntireColumn.Hidden = False
        
        Case Is = "Credit Card"
            Cells.EntireColumn.Hidden = False
            Set Rng3 = FindHdg3.CurrentRegion
            ColsToHide.EntireColumn.Hidden = True
            Rng3.EntireColumn.Hidden = False
    End Select

End Sub

    05:42 – Save file as macro-enabled