Show or Hide an Entire Column on Cell click in Excel using VBA Macro

You can dynamically hide or show an entire column or multiple columns in Excel using a simple VBA macro. Here in this post I am sharing an example where a macro will hide and show a column when someone clicks on a particular cell.

Hide or unhide an entire column on cell click in Excel using VBA

There can be many different scenarios, where you want to show/hide columns based on some conditions. Here’s my scenario. I have two columns with few rows of data in my Excel worksheet. I want my program to hide the 2nd column (Figures), when someone clicks on the B2 cell (see the image above). However, when someone again, clicks the C2 cell, it will unhide or show the hidden 2nd column.

• Create an Excel file and save the file as a macro (.xlsm format). Add two columns in Sheet1. Right click Sheet1 and choose View Code option. This will open the VBA editor. Copy and paste the below code in your editor.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        
        If Not Intersect(Target, Range("B2")) Is Nothing Then   ' Check if cell B2 is clicked.
        
            ' If yes, hide entire "B" column.
            Target.EntireColumn.Hidden = True
            
        ElseIf Not Intersect(Target, Range("C2")) Is Nothing Then
        
            ' Show (or unhide) the hidden "B" column when clicked on cell “C2”.
            Cells(Target.Row, Target.Column - 1).EntireColumn.Hidden = False        
            
        End If
    End If
End Sub

I have written my macro inside the Worksheet_SelectionChange event. VBA will call this event whenever a user clicks on a cell (any cell) in Sheet1. However, I want it to take an action, when the user clicks in either cell B2 or C2.

Related: How to Change the Column Width and Row Height in Excel using VBA

See the first condition, where I have used the EntireColumn property. In-fact, both the conditions have it. This property returns an object that represents the entire column. Once I have the entire column or the range, I am using the Hidden property to hide or unhide the column.

Target.EntireColumn.Hidden = True

The Hidden property in VBA returns a Variant that indicates if the columns are hidden. You can set its value as True or False.

The Hidden property can also be used to hide and unhide rows.

In the ElseIf condition, the macro checks if cell C2 is clicked. Here I have set the Hidden property as False.

Note: Properties and their values in VBA are case sensitive.

← PreviousNext →