VBA Macro to Hide All Columns That Contain a Value in a Cell - Excel Campus
37

VBA Macro to Hide All Columns That Contain a Value in a Cell

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.

Hide All Columns That Contain a Value - Excel VBA Macro - Before and After

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.

Hide Columns That Contain A Value In A Cell VBA Macro.xlsm (64.9 KB)

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:

  1. This macro loops through all the cells in Range(“A1:G1”) using a For Loop.
  2. The If statement checks the cell’s value to see if it equals X.
  3. 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.

Hide Unhide Columns That Contain Value in Cell Macro Button

We can also use a Slicer if you have multiple categories of columns you want to hide.

The Filter Columns Macro Thumbnail 640x360

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.

Additional Resources:

Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 37 comments
Rob - December 5, 2017

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

Reply
Tracey - October 26, 2017

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!!

Reply
Nick - September 22, 2017

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?

Reply
ES - September 5, 2017

Hi Jon,

Just dropping a quick line to say thank you! This is really great!

ES

Reply
ESalud - September 5, 2017

Hi Jon,

Just dropping a quick line to say thank you! This is really great!

Emeritus

Reply
Nithin - August 24, 2017

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

Reply
meriglen - August 12, 2017

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.

Reply
Ong Chin Hooi - August 12, 2017

Is very handy, great!

Reply
Uche Uche - August 11, 2017

I must confess that you are a great excel expert and nothing more to say than, THANKS.

Reply
Kenny K - August 11, 2017

Hi Jon,

Can the Macro be setup to loop through all tabs in the active workbook?

Cheers

Reply
Tanner - August 11, 2017

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.

Reply
Jeff F. - August 11, 2017

Excellent article. Very relevant! We really appreciate you providing this information–especially in such a clear way.

Reply
Tom - July 21, 2017

What if I want to hide the column if the cell condition is empty?

Reply
    Jon Acampora - July 21, 2017

    Hi Tom,
    If the cell is empty or blank, you can use the following.

    If c.Value = "" Then

    The double quotation mark represents a blank or nothing.

    Another option is to use the IsEmpty function.

    If IsEmpty(c) Then

    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.

    Reply
      Tom - July 21, 2017

      Thanks for the distinction. This is exactly what I needed.

      Reply
Tim - July 14, 2017

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?

Reply
    Jon Acampora - July 20, 2017

    Hi Tim,

    If the cell contains the name of the named range, you could something like the following.

    If c.Value = Range(Range("D3").Value).Value

    I’m not sure if that is exactly what you are looking for, but hopefully it helps.

    Reply
Jon Bixby - July 7, 2017

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?

Reply
    Jon Acampora - July 20, 2017

    Hi Jon,
    Sorry for the delayed reply. You can use an AND operator to evaluate both conditions.

    If c.Value = "X" And c.Offset(1,0).Value = "Y" Then

    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.

    Reply
Suzan de Lange - June 10, 2017

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?

Reply
me - May 2, 2017

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?

Reply
    Jon Acampora - June 1, 2017

    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.

    If UCase(c.Value) = "X" Then

    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!

    Reply
ANTON - April 29, 2017

Hi Jon
Thanks for the lesson, it’s very usefull…

Reply
Jeremia - April 20, 2017

How if I want to hide the value above 0

what code should i input?

Thanks

Reply
Simon - April 19, 2017

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)

Reply
Kristin - March 27, 2017

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.

Reply
    Jon Acampora - March 29, 2017

    Hi Kristin,
    Great question! You can modify the following line:

    If c.Value = Range("D3").Value Then

    That will compare the value of the cell in the loop (c.Value) to the value in cell D3. Thanks!

    Reply
Jane - March 26, 2017

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.

Reply
    Jon Acampora - March 29, 2017

    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.

    Reply
Christine Johnson - March 16, 2017

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?

Reply
    Jon Acampora - March 20, 2017

    Hi Christine,
    Great question! Yes, we can use an Or condition for this. Here is an example.

    If c.Value = "X" Or C.value = "Y" Then

    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.

    Reply
Ganesh - February 3, 2017

Thanks a lot.

Reply
Kunaal Umarwadia - February 2, 2017

Thank you so much, this was fantastic!

Reply

Leave a Reply: