Click a cell to make a column hidden or visible [VBA]
What's on this page
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?
- 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.
Animated image
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.
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.
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
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 […]
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 […]
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 […]
5 Responses to “Click a cell to make a column hidden or visible [VBA]”
Leave a Reply to mahmoud-lee
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.
Since the code is a toggle, you can eliminate the If..Then..Else..EndIf block and just use a logical expression instead...
Rick Rothstein (MVP - Excel),
Great, thank you for commenting!
Why is not useful code on Office 2003
Hello,
How could I have multiple sets of columns in one sheet that have the show/hide functionality?
Nevermind... I figured out I can just do multiple IF statements in the same Sub.