This article explains how to write a macro to hide all columns that contain a value in a cell. This can be any word, text, number, or phrase in a cell of the column.
In this example, Victor has a sales report and he wants to run a macro to hide columns with small number values. He has a formula in row 1 that returns an “X” if the column should be hidden.
So we need a macro that will loop through row 1 and hide each column if the cell in the column contains an “X”.
Download the Excel File
Download the example Excel file that contains the code samples.
VBA Macro Code Sample: Hide All Columns Containing a Value
The following macro will hide all the column containing an X in each cell in row 1. Here is a brief description of how the code works:
- This macro loops through all the cells in Range(“A1:G1”) using a For Loop.
- The If statement checks the cell's value to see if it equals X.
- If the cell value equals X then the cell's entirecolumn is hidden.
You can copy/paste the code to your own workbook.
Sub Hide_Columns_Containing_Value() 'Description: This macro will loop through a row and 'hide the column if the cell in row 1 of the column 'has the value of X. 'Author: Jon Acampora, Excel Campus 'Source: Dim c As Range For Each c In Range("A1:G1").Cells If c.Value = "X" Then c.EntireColumn.Hidden = True 'You can change the property above to False 'to unhide the columns. End If Next c End Sub
Unhide All Columns in a Range
You can change the Hidden property to False to unhide the columns. You could also run the following macro to unhide all the columns at one time.
Sub Unhide_All_Columns() 'This macro will unhide all the columns in the 'specified range. Range("A1:G1").EntireColumn.Hidden = False End Sub
The Range(“A1:G1”) reference can be changed to suite your needs.
To loop through all the cells in a row we could use the following line of code instead.
For Each c In Rows("1:1").Cells
The value “X” can also be changed to any number, text, phrase, or formula you want to evaluate for. You could even check the cell properties like font or fill color instead. The best way to get the code for these properties is using the macro recorder.
Toggle the Hidden State of a Column
We can also toggle the hidden property of the column. The following line of code will set the hidden property to the opposite of it's current state. If the column is hidden, it will be made visible (unhidden). If it's visible, it will be hidden.
c.EntireColumn.Hidden = Not c.EntireColumn.Hidden
The Not operator after the equal sign will set the property to the opposite. If the Hidden property is True, it will be set to False. If it is False, it will be set to True.
Here is the full macro for toggling the columns' hidden state.
Sub Hide_Columns_Toggle() 'Description: This macro will loop through a row and 'hide the column if the cell in row 1 of the column 'has the value of X. Dim c As Range For Each c In Rows("1:1").Cells If c.Value = "X" Then 'The following line changes the hidden property to 'the opposite of it's current setting for the column. c.EntireColumn.Hidden = Not c.EntireColumn.Hidden End If Next c End Sub
Loop Through All Sheets in the Workbook
Kenny asked a question about using this macro on all the sheets in the workbook. Here is a version of the macro that loops through all the sheets in the workbook, and runs the code on each sheet.
Sub Hide_Columns_Containing_Value_All_Sheets() 'Loop through all sheets and hide columns containing 'a value on each sheet. Dim c As Range Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets For Each c In ws.Rows("1:1").Cells If c.Value = "X" Then c.EntireColumn.Hidden = True 'You can change the property above to False 'to unhide the columns. End If Next c Next ws End Sub
Notice how we reference the ws variable in the following line.
For Each c In ws.Rows("1:1").Cells
This sets the c variable to reference the cell on the current worksheet (ws) in the loop. We do not have to reference the worksheet before c in the lines of code below it because the c variable (range object) is set to a cell on a specific sheet (ws).
Make it Interactive with Buttons
There are a few ways to make this macro interactive with buttons. We can add a simple form control button or shape to the sheet and assign the Hide_Columns_Toggle macro to it. Each time the user clicks the button the columns will either be hidden or unhidden, depending on their current state.
We can also use a Slicer if you have multiple categories of columns you want to hide.
Checkout my post on The Filter Columns Macro for details on this technique. This is a more advanced technique for hiding columns based on cell values.
Please leave a comment below with questions.