Click a cell to make a column hidden or visible [VBA]

This example demonstrates how to build a macro that hides/shows a specific column if a given cell is selected. Each time the cell is selected the column toggles between visible/hidden.

The image above shows an example that hides/shows column E if any cell in column D is selected. This is made possible using Event code which is somewhat different than regular VBA code.

Event code is stored in a worksheet or workbook module instead of a regular code module, I will show you in detail how it works.

Event code

'Event code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Check if any cell in column D is selected and that only one cell in column D is selected
If Not Intersect(Target, Range("D:D")) Is Nothing And Target.Cells.CountLarge = 1 Then

    'Check if column e is hidden
    If Range("E:E").EntireColumn.Hidden = True Then

        'Show column E
        Range("E:E").EntireColumn.Hidden = False

    'Continue here if column E is visible
    Else

        'Hide column E
        Range("E:E").EntireColumn.Hidden = True
    End If
End If
End Sub

Where to put the event code?

Click a cell to make a column hidden or visible insert event code

Right-click on the worksheet name, see image above.
Click "View Code". Visual Basic Editor opens, see image below.
Paste code to the worksheet module. The arrow in the image above shows where to put the event code.
Exit VB Editor and return to Excel.

Note, save the workbook with file extension *.xlsm (macro-enabled workbook) to attach the code to the workbook.

Animated image

Download Excel file

Click-to-hide-or-show-a-column-.xlsm

Click a button to hide/show a column

The animated image above shows that you can click a button that runs a macro to hide or show a given column. This macro is saved in a regular module.

The button is a Form Control that you can easily create from the Developer tab, I will show you how later in this article.

VBA code

'Name macro
Sub HideColumn()

'Check if column F is hidden
If Range("F:F").EntireColumn.Hidden = True Then

    'Show column F
    Range("F:F").EntireColumn.Hidden = False

'Continue here if column F is visible
Else

    'Hide column F
    Range("F:F").EntireColumn.Hidden = True
End If

End Sub

Where to put the macro code?

Copy above VBA code.
Press shortcut keys Alt + F11 to open the Visual Basic Editor (VBE).
Click "Insert" on the top menu, see image above.
Click "Module" from the drop-down list. A module appears in the Project Explorer to the left.
Paste VBA code to module window.
Exit VBE and return to Excel.

Note, save the workbook with file extension *.xlsm (macro-enabled workbook) to attach the code to the workbook.

How to create a button and link it to a macro

Click the "Developer" tab on the ribbon. Search the internet for "Show Developer tab" and your Excel version if it is missing.
Click "Insert" button on the "Developer" tab.
Click the button in the Form Controls group.
Move the mouse cursor where you want to place the button on the worksheet.
Click and hold with left mouse button.
Drag with mouse to build a button. Don't worry, you can change the size and location later.
Release left mouse button.
A dialog box appears.
Click the macro name, in this case, HideColumn.
Click OK button to dissmiss the dialog box.

Note. The button will be created if you click Cancel, however, no macro is linked. You can do that later if you prefer.
Right-click on the button, a pop-up menu appears. Click "Assign Macro...". The dialog box shows up again asking for a macro.

How to hide a row with a button

The example demonstrates a button that hides a specific row, click the button with the left mouse button and it will be hidden if the current state is visible and vice versa.

The animated image above shows a macro that hides entire row 5 when the user clicks on the button named "Hide/Show". The macro toggles row 5 between visible and hidden based on the current condition.

VBA Code

'Name macro
Sub HideRow()

'Check if row 5 is hidden
If Range("5:5").EntireRow.Hidden = True Then

    'Show row 5
    Range("5:5").EntireRow.Hidden = False

'Continue here if row 5 is visible
Else

    'Hide row 5
    Range("5:5").EntireRow.Hidden = True
End If

End Sub

The following VBA macro hides multiple rows, in this example row 5 and 7 are hidden.

'Name macro
Sub HideMultipleRows()

'Save two strings to array variable rW
rW = Array("5:5", "7:7")

'Iterate through values in array variable rW
For Each r In rW

'Check if row is hidden based on value in variable r
If Range(r).EntireRow.Hidden = True Then

    'Show row based on variable r
    Range(r).EntireRow.Hidden = False

'Continue here if row is visible
Else

    'Hide row
    Range(r).EntireRow.Hidden = True
End If

'Continue with next value in array
Next r

End Sub

Where to put the macro code?

How to link macro to the button?

Download Excel file

Click-to-hide-or-show-a-columnv2.xlsm

Save invoice data [VBA]

This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]

In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Identify missing numbers in a column

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

Working with COMBO BOXES [Form Controls]

This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Change chart data range using a Drop Down List [VBA]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

Excel calendar [VBA]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

Run a Macro from a Drop Down list [VBA]

This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two […]

Working with FILES

What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]

How to create an interactive Excel chart [VBA]

This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]

Comments (5)

5 Responses to “Click a cell to make a column hidden or visible [VBA]”

Rick Rothstein (MVP - Excel) says:

October 11, 2013 at 9:53 pm

Since the code is a toggle, you can eliminate the If..Then..Else..EndIf block and just use a logical expression instead...
```
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("D:D")) Is Nothing And Target.Cells.CountLarge = 1 Then
    Range("E:E").EntireColumn.Hidden = Not Range("E:E").EntireColumn.Hidden
  End If
End Sub
```
Reply
- Oscar says:
  
  October 12, 2013 at 9:02 am
  
  Rick Rothstein (MVP - Excel),
  
  Great, thank you for commenting!
mahmoud-lee says:

December 21, 2013 at 1:24 pm

Why is not useful code on Office 2003

Reply
Jan says:

August 24, 2020 at 6:07 pm

Hello,
How could I have multiple sets of columns in one sheet that have the show/hide functionality?

Reply
- Jan says:
  
  August 24, 2020 at 6:11 pm
  
  Nevermind... I figured out I can just do multiple IF statements in the same Sub.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form