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.

Hiding Columns Based on a Cell Value

by Allen Wyatt
(last updated February 6, 2016)

17

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.

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

MORE FROM ALLEN

Different Layouts for Footnotes

If you want to have footnotes appear in a different number of columns than what your text appears in, you may be out of luck. ...

Discover More

Converting Imported Information to Numeric Values

If the information you import into Excel is treated as text by the program, you may want to convert it to numeric values. ...

Discover More

Viewing Your Entire Document Width

The Zoom tool is very useful to help you see all of your document information. Here's how to make sure you can see all the ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

MORE EXCELTIPS (MENU)

Copying Formatting

Excel provides a couple of different ways to copy formatting from one cell to another. Perhaps the easiest way is to use the ...

Discover More

Converting Forced Text to Numbers

If you have some numbers stored in cells that are formatted as text, you may get some surprises when you try to use those ...

Discover More

Moving Custom Formats to Number Formatting Categories

Moving your custom formats into a formatting category other than "custom" isn't something you can do in Excel. Here's ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 8?

2017-03-09 07:26:45

Tom Philpott

Thanks for this! It's close to what I need to do but without the VBasic knowledge, I'm not able to adjust the code for my needs.

What I need is the following:
- A toggle) button which runs a macro which hides any row in which column A is empty
- At the same time, columns C-F should be hidden by the same macro (no matter what values are).

And if I click on the button, I'd like it to show all columns and all rows.

Is that possible?

THanks!

Tom


2017-03-02 12:52:53

GMM

Hey! I create a VBA code for hiding columns based on cell value of a specific range. I wanna to change specific range in dynamic range(vlookup)

Private Sub ComboBox1_Change()

End Sub

Private Sub Worksheet_Calculate()

Application.ScreenUpdating = False
Dim c As Range
For Each c In Range("L32:BE32")
c.EntireColumn.Hidden = (c.Value = 0)
Next c
Application.ScreenUpdating = True
End Sub

I wanna to change specific range(''L32:BE32") with a vlookup formula who search specific word and modify in code. I mean, if I insert or delete some rows in sheet, I must to change manually VBA code to new (L99:BE99)(ex) to can code work.
I wanna to do that automatic(I think with vlookup search based on specific criteria) and change L32:BE32 in L99:BE99 were 32/99 is a row number in sheet.

Can you help me? Thx in advance!

P.S.: Sry for grammatical error, is not my language!


2017-02-17 18:39:15

scott

'For Chintan, enter this macro and run it. Maybe add a button and assign it this macro

Sub dates()
Dim i As Long
Dim lc As Long
lc = Cells(2, Columns.Count).End(xlToLeft).Column
Dim dstart As Date, dend As Date
dstart = InputBox("Enter a start date, mm/dd/yyyy")
dend = InputBox("Enter an end date, mm/dd/yyyy")


For i = 1 To lc
If Cells(2, i) > dstart And Cells(2, i) < dend Then
Cells(2, i).EntireColumn.Hidden = False
Else
Cells(2,i).EntireColumn.Hidden = True
End If
Next i


End Sub


2017-02-10 04:46:26

Chintan

Hi I am trying to find a solution for display columns only between two dates. As my model has 365 columns each corresponding to a day in a year. So want to hide colums for date input provided by me. Say 1st Feb to 15th Feb. Then it should automatically hid all colums excepts pertaining to above dates between two dates.
will sincerely appreciate your advice on this guys.


2016-12-16 16:37:47

Kush

Hi,
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?

Kush


2016-12-14 12:00:36

Andy

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,
Andy


2016-12-14 10:43:49

Willy Vanhaelen

@Andy

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.


2016-12-13 17:48:25

Andy

Hi,

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
Else
Columns("H").EntireColumn.Hidden = False
End If
End Sub


2016-09-06 21:24:28

Tony Waugh

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.


2016-07-25 01:08:56

Aram

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.


2016-06-02 09:50:52

Paula

Is there a way to hide the cell if the return is FALSE from an assigned condition?


2016-05-20 19:15:16

Brian

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.


2016-05-16 21:29:44

Holly

The second piece of code works well for an excel problem I have. Is there anyway to make the code work automatically?


2016-04-20 22:18:49

Raul Bahena

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


2016-03-08 09:49:51

Willy Vanhaelen

@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


2016-03-08 04:25:15

Utkarsh Singh

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?


2016-02-07 02:58:57

Mark Cramoysan

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.


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share