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 list1hide 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

Related Posts

Find and Replace Multiple Values

This post will guide you how to find and replace multiple values at once with VBA macro or using formula  in Excel. How do I make multiple find and replace in Excel. Suppose that you have a few cells containing ...

VBA Macro For VLOOKUP From Another Sheet

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

How To Insert Comments in Protected Worksheet in Excel

This post will show you how to allow comments in a protected worksheet in Excel. You can easily to insert comments into cells in a normal worksheet in Excel, but if want to insert a comment in a worksheet that ...

How To Convert Text to Upper Cases(Using VBA) in Excel

This post will show you how to switch from lower case to upper case in Excel. and I am going to show you two different ways of converting text to upper cases using formula or VBA macro in Excel 2013,Excel ...

How To Hide Every Other Row in Excel (Using VBA)

This post will show you how to hide alternate rows or columns in Excel or how to hide every third, fourth, fifth row or column in Excel. If you want to hide every other row in your current worksheet, how ...

Comments

So empty here ... leave a comment!

Leave a Reply

Sidebar