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?
- Press with right mouse button on on the worksheet name, see image above.
- Press with left mouse button on "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
Press with left mouse button on a button to hide/show a column
The animated image above shows that you can press with left mouse button on 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).
- Press with left mouse button on "Insert" on the top menu, see image above.
- Press with left mouse button on "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
- Press with left mouse button on the "Developer" tab on the ribbon. Search the internet for "Show Developer tab" and your Excel version if it is missing.
- Press with left mouse button on "Insert" button on the "Developer" tab.
- Press with left mouse button on the button in the Form Controls group.
- Move the mouse cursor where you want to place the button on the worksheet.
- Press 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.
- Press with left mouse button on the macro name, in this case, HideColumn.
- Press with left mouse button on OK button to dissmiss the dialog box.
Press with right mouse button on on the button, a pop-up menu appears. Press with left mouse button on "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, press with left mouse button on 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 press with left mouse button ons 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
How to link macro to the button?
Macro category
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
Excel categories
5 Responses to “Click a cell to make a column hidden or visible – VBA”
Leave a Reply
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.