How to Hide or Unhide Columns Based on Drop-Down Selection in Excel

This post will guide you how to hide columns based on drop down list selection in Excel. How do I hide or unhide columns based on a dropdown validation list using VBA Macro in Excel 2013/2016.

Hide or Unhide Columns based on Drop-down List Box


Assuming that you have a drop-down list in Cell A2 with two values “TRUE” and “FALSE”. And both two Column B and Column C contain some text string values. I want to hide the entire Column B when the TRUE item is selected in the drop down list. And I want to hide the entire Column C when the FALSE item is select in the drop-down list in Cell A2. How can do it? This post will show you how to use an Excel VBA Macro to hide or unhide a specific column based on the selection in the Drop-down list box. Just do the following steps;

Step1: you need to create you drop-down list with TRUE and FALSE items in Cell A2. (just go to DATA tab, click Data Validation command, then you create a drop-down list in Cell A2)

hide or unhide columns based on dropdown list1 hide or unhide columns based on dropdown list2

Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step3: then the “Visual Basic Editor” window will appear.

Step4: Double click on the current sheet name in the VBAProject section to open the code window.

hide or unhide columns based on dropdown list5

Step5: paste the below VBA code into the code window. Then clicking “Save” button.

hide or unhide columns based on dropdown list4

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = ("$A$2") Then
        If Target.Text = "TRUE" Then
            Columns("B").EntireColumn.Hidden = True
            Columns("C").EntireColumn.Hidden = False
        ElseIf Target.Text = "FALSE" Then
            Columns("B").EntireColumn.Hidden = False
            Columns("C").EntireColumn.Hidden = True
        End If
    End If
End Sub

Note: the $A1$2 is the Cell Reference of the drop down list in your worksheet.

Step6: close the current Visual Basic Editor window, and you can try to select TRUE or FALSE item from the drop down list box, and you should see that the column B or Column C is hidden or unhidden.

hide or unhide columns based on dropdown list3

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar