How to hide or unhide columns based on drop down list selection in Excel?
While using Excel, you can hide or unhide specific columns based on the selection of a drop down list. For example, if you select No in the drop down list, column C to I will be hidden, but if you select Yes, the hidden columns C to I will be unhidden. See below screenshot shown.
In this article, we will show you a VBA method to hide or unhide columns based on drop down list selection in Excel.
Hide or unhide columns based on drop down list selection in Excel
Hide or unhide columns based on drop down list selection in Excel
As above example mentioned, to hide or unhide columns C to I based on the drop down list selection, please do as follows.
1. First, create your drop down list with Yes and No which you need.
2. Then press Alt + F11 to open the Microsoft Visual Basic for Application window.
3. Double click the current open sheet name in the VBAProject section to open the Code editor.
4. Then copy and paste below VBA code into the Code editor.
VBA code: hide or unhide columns based on drop down list selection
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180822
If Target.Column = 2 And Target.Row = 3 Then
If Target.Value = "No" Then
Application.Columns("C:I").Select
Application.Selection.EntireColumn.Hidden = True
ElseIf Target.Value = "Yes" Then
Application.Columns("C:I").Select
Application.Selection.EntireColumn.Hidden = False
End If
End If
End Sub
Note: In the above code, Column = 2 and Row = 3 is the cell reference of the drop down list, and the range C:I is the columns that you want to hide or unhide, .please change them to your need.
5. Press Alt + Q keys simultaneously to exit the Microsoft Visual Basic for Application window.
From now on, when you select No in the drop down list, all specified columns are hidden.
But if you select Yes in the drop down list, all hidden columns are displayed immediately.
Related articles:
- How to auto populate other cells when selecting values in Excel drop down list?
- How to autocomplete when typing in Excel drop down list?
- How to create a drop down list calendar in Excel?
- How to create a searchable drop down list in Excel?
- How to create drop down list with multiple selections or values in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!