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)
Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
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.
Step5: paste the below VBA code into the code window. Then clicking “Save” button.
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.