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