Excel.Tips.Net ExcelTips (Menu Interface)

Hiding Columns Based on a Cell Value

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Hiding Columns Based on a Cell Value.

Excel's great conditional formatting capabilities allow you to change the formatting of cells based on the content of a cell. There is no way, unfortunately, to easily hide entire columns of data based on the value of a particular cell.

You can, however, achieve the desired effect by using a macro to analyze the cell and adjust the Hidden attribute of the row you want to conditionally hide. The following simple macro, for instance, examines the contents of cell B4 and, if the cell contains 0, hides column H. If cell B4 does not contain 0, then column H is displayed.

Sub HideColumn1()
    If Range("B4").Value = 0 Then
        Columns("H").EntireColumn.Hidden = True
        Columns("H").EntireColumn.Hidden = False
    End If
End Sub

If you want the hiding and unhiding of the column to be done in real time, you can use the following version of the macro. Just make sure that you put this version in the code window for the worksheet on which you want it to work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B4").Value = 0 Then
        Columns("H").EntireColumn.Hidden = True
        Columns("H").EntireColumn.Hidden = False
    End If
End Sub

Notice that the guts of the two macros are the same. The only difference is that the second version is triggered by an event within Excel—the changing of which cell is currently selected. This means that every time you move from one cell to another, the value in B4 is checked and column H is either hidden or unhidden.

If it is possible that the contents of cell B4 could be empty, then it is possible that Excel will interpret that emptiness as a zero value. In that case, you can modify the macro just a bit so that it checks for an empty cell.

Sub HideColumn2()
    Dim rCell As Range
    Set rCell = Range("B4")

    Columns("H").EntireColumn.Hidden = False
    If (Not IsEmpty(rCell))
      And (IsNumeric(rCell)
      And (rCell.Value = 0) Then
        Columns("H").EntireColumn.Hidden = True
    End If
End Sub

This version of the macro actually checks three conditions: that B4 is not empty, that it contains a numeric value, and that the value is 0. If all three of these conditions are met, then column H is hidden.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3283) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Hiding Columns Based on a Cell Value.

Related Tips:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Kush    16 Dec 2016, 16:37
What if I have a file where I want raws to to be visible for data entry only if they meet a certain criteria. Eg, I have workers who keep on asking for casuals though we are well establised. We have a excel file where they fill the data but it open so they currently fill person. I have a formula that calculates staff on duty, on sick leave or on vacation and it gives the variance. So is it possible to unhide the cells based on variance number?

Andy    14 Dec 2016, 12:00
Thanks Willy!!! It works! I had it in a normal VBA Developer section but didn't know about the sheet's code page.

Thanks again,
Willy Vanhaelen    14 Dec 2016, 10:43

Here is a one liner that does the job:

Private Sub Worksheet_Change(ByVal Target As Range)
   Columns("H").EntireColumn.Hidden = [B4] = 0
End Sub

It is very important that you put this macro in the sheet's code page. Right click the sheet's tab and select "View Code". That's the place to be.
Andy    13 Dec 2016, 17:48

I tried using the original code for hiding columns in real time but it doesn't seems to work in my excel 2010 with .xlsm. Anything I'm doing wrong?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B4").Value = 0 Then
        Columns("H").EntireColumn.Hidden = True
        Columns("H").EntireColumn.Hidden = False
    End If
End Sub
Tony Waugh    06 Sep 2016, 21:24
I wish to hide a column in a worksheet contained in a workbook with several worksheets when a cell contains the word "Choose..." and unhide when it contains other text.
Aram    25 Jul 2016, 01:08
Hi, I would like some help on being able to hide rows based on data being available/not available in three columns at the same time. So I would like excel (VBA code) to check if I17, J17 and K17 (up to I300, J300 and K300) columns have any data in them or not... if not then hide the entire row. Whats the code for this in its entirety. I am new at VBA so I am unable to write it. Any help will be greatly appreciated.
Paula    02 Jun 2016, 09:50
Is there a way to hide the cell if the return is FALSE from an assigned condition?
Brian    20 May 2016, 19:15
Great post, quick question.

If I wanted cell B4 to hide column H when there was a text word instead of a value how would I do that? Example when B4 has the word "Apple" then column H would hide.
Holly    16 May 2016, 21:29
The second piece of code works well for an excel problem I have. Is there anyway to make the code work automatically?
Raul Bahena    20 Apr 2016, 22:18
Hi Allen,

Thank you for your commitment to this site and overall passion for sharing knowledge. I was able to modify the code as follows to auto hide/unhide column range based on value of specific cell address and is working beautifully!! Please see below and wish you continue success.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B23").Value = 1 Then
        Columns("C:T").EntireColumn.Hidden = True
        Columns("C:T").EntireColumn.Hidden = False
    If Range("B23").Value = 2 Then
        Columns("D:T").EntireColumn.Hidden = True
       Columns("D:T").EntireColumn.Hidden = False
    If Range("B23").Value = 3 Then
        Columns("E:T").EntireColumn.Hidden = True
       Columns("E:T").EntireColumn.Hidden = False
    If Range("B23").Value = 4 Then
        Columns("F:T").EntireColumn.Hidden = True
       Columns("F:T").EntireColumn.Hidden = False
    If Range("B23").Value = 5 Then
        Columns("G:T").EntireColumn.Hidden = True
       Columns("G:T").EntireColumn.Hidden = False
    If Range("B23").Value = 6 Then
        Columns("H:T").EntireColumn.Hidden = True
       Columns("H:T").EntireColumn.Hidden = False
    If Range("B23").Value = 7 Then
        Columns("I:T").EntireColumn.Hidden = True
       Columns("I:T").EntireColumn.Hidden = False
    If Range("B23").Value = 8 Then
        Columns("J:T").EntireColumn.Hidden = True
       Columns("J:T").EntireColumn.Hidden = False
    If Range("B23").Value = 9 Then
        Columns("K:T").EntireColumn.Hidden = True
       Columns("K:T").EntireColumn.Hidden = False
    If Range("B23").Value = 10 Then
        Columns("L:T").EntireColumn.Hidden = True
       Columns("L:T").EntireColumn.Hidden = False
    If Range("B23").Value = 11 Then
        Columns("M:T").EntireColumn.Hidden = True
       Columns("M:T").EntireColumn.Hidden = False
    If Range("B23").Value = 12 Then
        Columns("N:T").EntireColumn.Hidden = True
       Columns("N:T").EntireColumn.Hidden = False
    If Range("B23").Value = 13 Then
        Columns("O:T").EntireColumn.Hidden = True
       Columns("O:T").EntireColumn.Hidden = False
    If Range("B23").Value = 14 Then
        Columns("P:T").EntireColumn.Hidden = True
       Columns("P:T").EntireColumn.Hidden = False
    If Range("B23").Value = 15 Then
        Columns("Q:T").EntireColumn.Hidden = True
       Columns("Q:T").EntireColumn.Hidden = False
    If Range("B23").Value = 16 Then
        Columns("R:T").EntireColumn.Hidden = True
       Columns("R:T").EntireColumn.Hidden = False
    If Range("B23").Value = 17 Then
        Columns("S:T").EntireColumn.Hidden = True
       Columns("S:T").EntireColumn.Hidden = False
    If Range("B23").Value = 18 Then
        Columns("T:T").EntireColumn.Hidden = True
       Columns("T:T").EntireColumn.Hidden = False
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
End Sub
Willy Vanhaelen    08 Mar 2016, 09:49
@Utkarsh Singh


Worksheet_SelectionChange(ByVal Target As Range)


Worksheet_Change(ByVal Target As Range)

But you can make it even a one liner:

Private Sub Worksheet_Change(ByVal Target As Range)
   Columns("H").EntireColumn.Hidden = Range("B4") = 0
End Sub
Utkarsh Singh    08 Mar 2016, 04:25
The macro works great but suppose if the cell value is a list (like Data Validation list" then if we select one option which is zero then it does not refresh immediately. One has to deselect that cell and move to other cell for result to be shown.
Does anybody know how to fix this?
Mark Cramoysan    07 Feb 2016, 02:58
Of course if you just want the data to disappear then a Conditional Format can be the answer - I use white writing on a white background for all sorts of things where the data needs to not be immediately visible but so it can be seen if needed.

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2017 Sharon Parq Associates, Inc.