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