ExcelTips (Menu Interface)

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...

Helpful Links

ExcelTips FAQ
ExcelTips Resources
Ask an Excel Question
Make a Comment

Free Business Forms
Free Calendars

Tips.Net > ExcelTips Home > Formatting > Hiding Columns Based on a Cell Value

Hiding Columns Based on a Cell Value

by Allen Wyatt (last updated February 6, 2016)

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

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

Leave your own comment:

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


	Commenting Terms

Comments for this tip:

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.

Raul.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B23").Value = 1 Then
        Columns("C:T").EntireColumn.Hidden = True
    Else
        Columns("C:T").EntireColumn.Hidden = False
    If Range("B23").Value = 2 Then
        Columns("D:T").EntireColumn.Hidden = True
    Else
       Columns("D:T").EntireColumn.Hidden = False
    If Range("B23").Value = 3 Then
        Columns("E:T").EntireColumn.Hidden = True
    Else
       Columns("E:T").EntireColumn.Hidden = False
    If Range("B23").Value = 4 Then
        Columns("F:T").EntireColumn.Hidden = True
    Else
       Columns("F:T").EntireColumn.Hidden = False
    If Range("B23").Value = 5 Then
        Columns("G:T").EntireColumn.Hidden = True
    Else
       Columns("G:T").EntireColumn.Hidden = False
    If Range("B23").Value = 6 Then
        Columns("H:T").EntireColumn.Hidden = True
    Else
       Columns("H:T").EntireColumn.Hidden = False
    If Range("B23").Value = 7 Then
        Columns("I:T").EntireColumn.Hidden = True
    Else
       Columns("I:T").EntireColumn.Hidden = False
    If Range("B23").Value = 8 Then
        Columns("J:T").EntireColumn.Hidden = True
    Else
       Columns("J:T").EntireColumn.Hidden = False
    If Range("B23").Value = 9 Then
        Columns("K:T").EntireColumn.Hidden = True
    Else
       Columns("K:T").EntireColumn.Hidden = False
    If Range("B23").Value = 10 Then
        Columns("L:T").EntireColumn.Hidden = True
    Else
       Columns("L:T").EntireColumn.Hidden = False
    If Range("B23").Value = 11 Then
        Columns("M:T").EntireColumn.Hidden = True
    Else
       Columns("M:T").EntireColumn.Hidden = False
    If Range("B23").Value = 12 Then
        Columns("N:T").EntireColumn.Hidden = True
    Else
       Columns("N:T").EntireColumn.Hidden = False
    If Range("B23").Value = 13 Then
        Columns("O:T").EntireColumn.Hidden = True
    Else
       Columns("O:T").EntireColumn.Hidden = False
    If Range("B23").Value = 14 Then
        Columns("P:T").EntireColumn.Hidden = True
    Else
       Columns("P:T").EntireColumn.Hidden = False
    If Range("B23").Value = 15 Then
        Columns("Q:T").EntireColumn.Hidden = True
    Else
       Columns("Q:T").EntireColumn.Hidden = False
    If Range("B23").Value = 16 Then
        Columns("R:T").EntireColumn.Hidden = True
    Else
       Columns("R:T").EntireColumn.Hidden = False
    If Range("B23").Value = 17 Then
        Columns("S:T").EntireColumn.Hidden = True
    Else
       Columns("S:T").EntireColumn.Hidden = False
    If Range("B23").Value = 18 Then
        Columns("T:T").EntireColumn.Hidden = True
    Else
       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

Change

Worksheet_SelectionChange(ByVal Target As Range)

in

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.