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.
Thank you so much, this was fantastic!
Thanks a lot.
Awesome. But I want it to find 2 or more “X” items and hide all. Is there a way to have it find multiple cell values?
Hi Christine,
Great question! Yes, we can use an Or condition for this. Here is an example.
That would check if the cell value is X or Y, and perform the code below if EITHER condition is met.
I hope that helps.
how would I apply this to run across multiple sheets in the same workbook?
I have 4 sheets all with multiple columns with the word “preview” in row 1 (column numbers vary), I need to hide and unhide thee depending on the day of the week.
Hi Jane,
You can use another loop to loop through the sheets, then run the code on each sheet.
Dim ws as Worksheet
Dim c As Range
For Each ws in ActiveWorkbook.Worksheets
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
Next ws
I hope that helps.
How do you do this in reference to a value in a cell?
Example, if the value in cell range G9:J9 equals the value in cell D3 I want those columns to remain visible and if not I want those columns hidden.
Hi Kristin,
Great question! You can modify the following line:
That will compare the value of the cell in the loop (c.Value) to the value in cell D3. Thanks!
Hi Jon,
Thanks for the post.
How do i refere to the list of values kept in cells I2:I6 in the sheet named “variables” in the same workbook (I don t want to use multiple ORs)
Hi Simon,
You can use a VLOOKUP or COUNTIF formula for that. Here is an article on how to use COUNTIF instead of VLOOKUP. I hope that helps. Thanks! 🙂
How if I want to hide the value above 0
what code should i input?
Thanks
Hi Jon
Thanks for the lesson, it’s very usefull…
Hello,
Just a couple of questions:
1) How would you modify the code to run automatically rather than running the macro
2) The target item seems to be case sensitive. I tried your code and it worked when my value was “X” (capital x) but not “x” (lower case x). Is there a way to make it non-case-sensitive?
Hi me,
1) Here is a video on how to use events in VBA to run code based on user actions.
2) We can use the UCase function to convert the value to of the cell to upper case when the macros performs the test.
That will convert c.Value to an uppercase string of text. It will NOT convert the value in the cell to upper case. It is only doing the conversion in memory to test against the upper case “X”.
Thanks!
Good day Jon,
My code gives an error after hiding 25 columns:
If instr(“A”, c.value) = 0 then c.EntireColumn.Hidden = True
When I use the code with different letters (“B” or “C” instead of “A”) the same happens but the code stops at different c’s (always the 26th c to be hidden)
Have you seen this before?/do you perhaps know how to solve this?
What if I wanted to hide a column based on two criteria in two different rows? Say A1:G1 contains an X and A2:G2 contains a Y. How would the code change if wanted only columns that have both an X and a Y to be hidden?
Hi Jon,
Sorry for the delayed reply. You can use an AND operator to evaluate both conditions.
The Offset property allows us to offset a certain number of rows or columns from the range (cell).
c.Offset(1,0) will go down 1 row and to the right 0 columns. The column parameter is optional, so we could also use c.Offset(1).
I hope that helps.
Hi Jon,
Similar to Kristen’s question above on referencing a cell value:
If c.Value = Range(“D3”).Value Then
I am having issues getting this to work when my cell “D3” is a named range using data validation. The macro does not seem to recognize the text in that cell. Any workarounds?
Hi Tim,
If the cell contains the name of the named range, you could something like the following.
I’m not sure if that is exactly what you are looking for, but hopefully it helps.
What if I want to hide the column if the cell condition is empty?
Hi Tom,
If the cell is empty or blank, you can use the following.
The double quotation mark represents a blank or nothing.
Another option is to use the IsEmpty function.
The IsEmpty function returns a True or False value. So, if the cell (c) is empty then the function will return a True and the column will be hidden.
The difference between the two techniques is that the first is checking if the Value of the cell is blank. We can have formulas in the cell that return a blank, and the condition would evaluate to True.
The IsEmpty function is checking to see if the cell is empty, meaning there is no formula or value in it. If the cell is completely empty then IsEmpty will return a True.
I hope that helps.
Thanks for the distinction. This is exactly what I needed.
Excellent article. Very relevant! We really appreciate you providing this information–especially in such a clear way.
Thank you Jeff! I appreciate the nice feedback. 🙂
Thank you Jon. Excellent code. I would love to see (or write) how to cycle through all rows starting with row 2, and find a string (let’s say “Closed”) in a specific column, and then delete the entire row that contains that call string.
Thank you for reading.
Hi Jon,
Can the Macro be setup to loop through all tabs in the active workbook?
Cheers
Hey Kenny,
Great question! I added a section in the article above on how to loop through all sheets in the workbook and run the code on each sheet.
Thanks!
I must confess that you are a great excel expert and nothing more to say than, THANKS.
Thank you so much Uche! 🙂
Is very handy, great!
Sir Jon,
I want to ask I want to make data entry form in vba user form. 1 combo box which contains all of the list items then 1 text box where i can manually input the amount.
how I can make code of data entry user form which combo box list item when selected will go to the sheet where the selected item located then the text box entered amount would be recorded next to the selected item.
I have a sheet where I listed all the items.
Hi Jon,
I have a concern on adding macros to a Program request form:-
Number of sections=100
Number of activity=30
If we select one section and 2 activity at a time only those details should get appear, so likewise the same for if we select 100 sections and 30 activity, the same should get reflected, can you help me in this, while I checked with those who are experienced in excel they said in brief that it is nothing but hiding and UN-hiding the columns, but the thing is that I am new to this macro world, so can you help me in putting macros for the same.
Thanks & Regards,
Nithin A R
Hi Jon,
Just dropping a quick line to say thank you! This is really great!
Emeritus
Hi Jon,
Just dropping a quick line to say thank you! This is really great!
ES
If I do this with a slightly different range:
[Code]
Dim c As Range
For Each c In Range(“A1:CD200”).Cells
If c.Value = “Sample_Type” Then
c.EntireColumn.Hidden = True
End If
Next c
[code]
I get the Run-time error ’13’: Type Mismatch.
Is there a way to get around this?
Help
I am a complete novice at Macros, so am adapting your macro to check the values in row 5 of each column and if it says Completed, then to hide the entire column.
Can you write out how it should be laid out because I have tried to adapt it and it does not like the line
c.EntireColumn.Hidden = Not
It says
Compile error:
Expected:expression
What am I doing wrong!!
I’ve got the below:
Private Sub ToggleButton6_Click()
If ToggleButton1.Value Then
Call Module1.Hide_Columns_Containing_Value
ToggleButton6.Caption = “Unhide Columns”
Else
ToggleButton6.Caption = “Hide Columns”
Call Module1.Unhide_All_Columns
End If
End Sub
But getting error (highlighted yellow when I clicked debug) on the 3rd last line:
Call Module1.Unhide_All_Columns
I have one excel sheet and I want to filter it with Vendo/Team wise is in N column and week wise is in T column by using vba code
below is the snapshot for refrence can any one help me
Vendor/Team Work Week
Red Vision WK1
Savant WK1
Savant WK1
Savant WK1
String WK1
Savant WK1
Ameristar WK1
Ameristar WK1
Savant WK1
Savant WK1
Savant WK1
String WK1
Savant WK1
Red Vision WK1
Red Vision WK1
Savant WK1
String WK1
Savant WK1
Savant WK1
Savant WK1
Savant WK1
Savant WK1
@Jon,
my hide-statement like that:
Sub Hide_Columns()
Dim lastCol As Long, c As Range
‘since Excel 2003 every hide-command
‘trigger a calculation!
Application.EnableEvents = False
With ActiveSheet
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
For Each c In Range(“A1”).Resize(1, lastCol)
If c.Value = “X” Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next
Application.EnableEvents = True
End Sub
salute Luschi from klein-Paris
Hi Jon,
Thank you for this wonderful article. Is there any way we can hide a range of data instead of hiding entire column or row(for example: A2:D8)?
Thanks & Regards,
Preet
Hi Preet,
You would have to hide the entire rows and columns for the range. You can’t just hide the cells within a range. You could do something like change the font color of the cells to white or place a shape over them, so they are not visible. I hope that helps.
Jon, thanks for sharing and helping us all!
I would like to run this macro when the workbook opens and I would also like to check two enitre rows, I managed to do the first (running the macro when the book opens) but not the second, I tried this code you previously suggested: “If c.Value = “X” And c.Offset(1,0).Value = “Y” Then” but it didn’t work for me.
The other value I would like the macro to look for is the error message #¡VALOR!
Is there a way I can send you an image so you can see what those rows are, or better yet, send you the workbook?
Thanks again!
Hi,
I have region wise like east,west,north & south data and i have to show that data by region wise with top row sum of the region by macro button, if click north button and show only north region data and show top in only north region sum.
please help……
Hi Jon,
What if I just want to hide columns in the pivot table which the grand total is 0, without hiding the other columns that have blank rows? I´ve tried the code below, however it hides the blank ones as well:
Sub HideZeroTotalColumns()
‘this works with the active sheet
‘in whatever book, and all you need
‘to do is select the book, sheet and
‘a cell in the “total” row to then
‘hide all columns with a zero total
Dim anyWS As Worksheet
Dim anyTotalRow As Range
Dim anyCell As Range
Set anyWS = ActiveSheet
Set anyTotalRow = anyWS.Range(Cells(ActiveCell.Row, 1).Address, _
Cells(ActiveCell.Row, _
anyWS.Cells(ActiveCell.Row, Columns.Count). _
End(xlToLeft).Column))
‘improve performance speed
Application.ScreenUpdating = False
‘unhide in case was hidden and is now
‘non-zero
anyTotalRow.Columns.EntireColumn.Hidden = False
For Each anyCell In anyTotalRow
If anyCell = 0 Then
anyCell.EntireColumn.Hidden = True
End If
Next
Set anyTotalRow = Nothing
Set anyWS = Nothing
End Sub
Thank you, great tip 🙂
Thanks – this is great!
Why couldn’t I do this (I’m trying to use a cell -c5- to reference the current month and hide all other columns that are not equal to that cell in row 5):
Sub This_Month()
Range (“d5:ag5”)
If Value = “c5” Then
EntireColumn.Hidden = True
End If
Next
End Sub
Hi
can anyone help and consider it for urgent
what if i wanted by tabbing to skip the cells that contains for example ( color gray ) in the same row.
can anyone fix my below code
Private Sub worksheet_selectionChange(ByVal target As Range)
Static sRg As Range
Dim columnoffset As Integer
If target.Count = “210” Then Exit Sub
If Not Intersect(target, Union([C:C ], [ E:E ])) Is Nothing Then
With target
Appication.EnableEvents = False
If Not sRg Is Nothing Then
If sRg.Column < .Column Then
columnoffset = 1
Elself .Column 1 Then
columnoffset = -1
End If
Else
columnoffset = 1
End If
.Offset(, columnoffset).Select
Application.EnableEvents = True
End With
End If
Set sRg = ActiveCell
End Sub
Thank you so much in advance
Hi Jon,
This is a great article and very close to what I want to achieve. Note my VBA skills are beginner at best.
What I want to do instead is hide a row if the value in a Column range of say E5:E300 = 0. So if for example E56 = 0, then row E is hidden.
If anyone could comment on how to achieve this it would be appreciated.
I meant to say “…,then Row 56 is hidden.”
This is the code adapted to what I need. Anyone care to comment? It seems to run slow at times, not sure why.
Sub Hide_Rows_Toggle()
‘Description: This macro will loop through a column and
‘hide the row if the cell in column E of the row
‘has the value of 0.
Dim c As Range
For Each c In Range(“E13:E220”).Cells
If c.Value = “0” Then
‘The following line changes the hidden property to
‘the opposite of it’s current setting for the column.
c.EntireRow.Hidden = Not c.EntireRow.Hidden
End If
Next c
End Sub
How do I copy and paste the data after I have ran the macro, to insert into a different excel spread sheet to populate a specific template. When I copy the Data and paste special into the other spreadsheet it doesn’t recognise the Macro setting and reverts back to the original setting PRE-Macro
how to do this with multiple checkboxes
It worked! Thanks a lot! Need help on hiding rows now. Hope you could help me.
Hi Jon,
I was going to group and then use a toggle button to hide/unhide columns.
But, what I’m really trying to do is completely hide columns without data (future data columns), but allow the viewer to hide/unhide data that is currently there.
Here’s an example: I have Jan-Mar columns for Q1 but #’s and a % only exist for Jan. I want to hide Feb.-Mar. Easy. But come Feb, I will enter in #’s and a % for Feb. and I want to still hide March but I want the viewer to choose to see the numbers and the percentage for Jan and Feb. or just the percentages. Does that make sense?
Hello all
I had been looking for this solution for about three hours and it was the best solution.
Thank you very much
I’m so glad I came to this website
Wishing you health
I am using the “Loop Through All Sheets in the Workbook” code, which is great, except I want to exclude some sheets that it applies to, could you give me the code for that? Thanks!
I’m using the code above and it works great, but i have a question:
How do you hide a column if the cell contains an “X”, but also has other characters in the cell?
I downloaded the samples files but they did not work. I am running windows 10. is that an issue?