How to Hide and Unhide Columns Based on a Drop-Down List Selection in Excel

How to Create a Drop-Down List in Excel

STEPS:

• Select a cell (E3, in this example) on which we’ll create the drop-down list.

• Go to the Data tab of the Excel Ribbon.
• Click on the Data Validation option.

• In the Data Validation window, select the Setting tab.
• In the Allow drop-down list, choose the List option.

• Type Active and Inactive in the Source input box and hit OK.

• As an output, we can see a drop-down list in cell E3 with two options to select- Active and Inactive.

Example 1 – Hide or Unhide Columns Based On Drop-Down List Selection in Excel

STEPS:

• Right-click on the sheet name and select the View Code option.

• Insert the following code in the visual code editor:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = ("$E$3") Then
        If Target.Text = "Active" Then
            Columns("C").EntireColumn.Hidden = True
            Columns("B").EntireColumn.Hidden = False
        ElseIf Target.Text = "Inactive" Then
            Columns("C").EntireColumn.Hidden = False
            Columns("B").EntireColumn.Hidden = True
        End If
    End If
End Sub

• Save the code by pressing Ctrl + S and close the code editor.
• In the worksheet, to hide the active members’ column e., keeping only the inactive members’ column, choose the Inactive option from the drop-down list.

• Select the Active option from the drop-down list.

• The column with active members appears, and the column with inactive members is hidden.

Code Explanation:

In our code,

• we used the EntireColumn property to select the entire column with active and inactive members.
• Then, we set the .hidden property to True or False to hide a specific column.

Read More: How to Remove Used Items from Drop Down List in Excel

Example 2: Hide or Unhide Columns to Filter Data Based On Drop-Down List Selection

The dataset contains sales data for 7 products from 3 different categories: Fruit, Vegetables, and Fish.

STEPS:

• In cell B2, create a drop-down list with 4 options- All, Fruit, Vegetable, and Fish.
  • Create a drop-down list in the Excel section that is described earlier in the article.

• To open the Visual Code Editor, right-click on the sheet name and choose the View Code option.

• Insert the following code into the editor:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim a As Variant, b As String
     If Target.Column = 2 And Target.Row = 2 Then
        b = Target.Value2
        With Range("C5:I5")
            Application.ScreenUpdating = False
            .EntireColumn.Hidden = (b <> "All")
            If b <> "All" Then
                For Each a In .Cells
                    If a = b Then a.EntireColumn.Hidden = False
                Next
            End If
            Application.ScreenUpdating = True
        End With
    End If
End Sub

• Save the code by pressing Ctrl + S and close the code editor.
• Our dataset is filterable based on the category we select from the drop-down list. The following screenshots show the outputs.

The first image is the list for the Fruit category.

Choose the Vegetable category.

The next image shows the Fish category list.

Choose all the categories.

Code Explanation:

• We selected the target cell B2 using the following line of code defining its column and row number. We did it differently in example 1 using the Address property.

   If Target.Column = 2 And Target.Row = 2 Then

• The variable b holds the value of the selected category in the drop-down.
• The following code defines the range of cells containing category names in the sale list. Each of the values is matched against the variable b.

 With Range("C5:I5")

• If the value of b matches with one of the values of Range(“C5:I5”), the code selects the entire column associated with the cell and keeps it visible by applying the Hidden property to False.

Read More: How to Remove Duplicates from Drop Down List in Excel

Things to Remember

In the VBA code, we set the Application.ScreenUpdating = False before starting the loop and again changing to Application.ScreenUpdating = True after finishing the loop to get a faster response while changing the selection in the drop-down list.

Download the Practice Workbook

Download this workbook to practice.

Related Articles

• How to Create Drop Down List in Multiple Columns in Excel
• Create a Searchable Drop Down List in Excel
• How to Add Blank Option to Drop Down List in Excel
• Creating a Drop Down Filter to Extract Data Based on Selection in Excel
• How to Select from Drop Down and Pull Data from Different Sheet in Excel
• How to Create a Form with Drop Down List in Excel
• How to Fill Drop-Down List Cell in Excel with Color but with No Text
• [Fixed!] Drop Down List Ignore Blank Not Working in Excel
• How to Make Multiple Selection from Drop Down List in Excel
• How to Autocomplete Data Validation Drop Down List in Excel

<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel