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.

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:

58 comments

Your email address will not be published. Required fields are marked *

  • 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.

      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.

  • 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:

      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!

  • 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)

  • 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.

      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!

  • 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.

      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.

  • 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.

      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.

    • 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.

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

  • 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.

  • 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

  • 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

  • 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.

    • 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

  • 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?

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter