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)

24

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

Word's Object Model

Understanding Word's Object Model and how it relates to macros in VBA.

Discover More

Preparing a Chart Sheet for Printing

One type of chart that Excel allows you to create is one that occupies an entire worksheet. When it comes time to print such ...

Discover More

Creating Files with Mail Merge

When you use mail merge to create a document that incorporates all your data source records, you end up with a large document ...

Discover More

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!

More ExcelTips (menu)

Dates with Periods

You may want Excel to format your dates using a pattern it doesn't normally use—such as using periods instead of ...

Discover More

Formatting Raw Data

When you get a bunch of raw data into Excel from an external source, it isn't going to be formatted to your liking. The ...

Discover More

No More Custom Formats Can Be Added

If you make too many formatting changes to your workbook, you could end up with a situation where you cannot make any such ...

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

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. Maximum image size is 6Mpixels. 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 seven less than 7?

2017-08-29 13:10:36

Monte Tucker

I was looking for a way of doing the same Data Filter that temporarily hides Rows based upon Column values, but I wanted to hide the Columns based upon Row values instead.

Sure, I could write a macro that pops up a window asking for the cell to match (or enter a value), and then hide each matching column using the selected cell (or Match or some similar method), but I was hoping on avoiding a macro if possible.


2017-08-17 14:55:37

Willy Vanhaelen

@MrVanderBrink
With Worksheet_SelectionChange the macro runs each time the cell pointer is moved even when you don't change anything.
With Worksheet_Change the macro only runs when you change anything in a cell.

The latter is to be preferred in most cases.


2017-08-16 05:17:09

MrVanderBrink

Dear Allen,
Reply to self

change
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
into
Private Sub Worksheet_Change(ByVal Target As Range)

helps a lot in speed (dont know why)

Still I am curious if it also can be fixed with using coulmns as variables. How would the code look like?


2017-08-16 05:10:37

MrVanderBrink

Dear Allen,
What would be a proper way to use this for multiple columns?
My example below really slows down the sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns("U").EntireColumn.Hidden = [U1] = 1
Columns("V").EntireColumn.Hidden = [V1] = 1
Columns("W").EntireColumn.Hidden = [W1] = 1
Columns("X").EntireColumn.Hidden = [X1] = 1
Columns("Y").EntireColumn.Hidden = [Y1] = 1
Columns("Z").EntireColumn.Hidden = [Z1] = 1
Columns("AA").EntireColumn.Hidden = [AA1] = 1
Columns("AB").EntireColumn.Hidden = [AB1] = 1
Columns("AC").EntireColumn.Hidden = [AC1] = 1
Columns("AD").EntireColumn.Hidden = [AD1] = 1
Columns("AE").EntireColumn.Hidden = [AE1] = 1
Columns("AF").EntireColumn.Hidden = [AF1] = 1
etc...
etc...
End sub

Thanks!


2017-07-18 18:10:57

Bec

I was able to hide columns based on the value in that column at a certain row (28 in this case).
This works as a 'horizontal filter'.

Columns("O").EntireColumn.Hidden = Range("O28").Value = ""
Columns("P").EntireColumn.Hidden = Range("P28").Value = ""
Columns("Q").EntireColumn.Hidden = Range("Q28").Value = ""
Columns("R").EntireColumn.Hidden = Range("R28").Value = ""
Columns("S").EntireColumn.Hidden = Range("S28").Value = ""
...etc for each column applicable. In my case, many columns!

However - I have many rows that I want to put this filter on.
I know I could repeat this macro for each row eg

Columns("O").EntireColumn.Hidden = Range("O29").Value = ""
Columns("P").EntireColumn.Hidden = Range("P29").Value = ""

but I don't want hundreds of macros assigned to separate buttons.
What I really need is one macro that is relative to the row it is triggered from - so I have been experimenting with Relative References.

Columns("O").EntireColumn.Hidden = ActiveCell.Offset(0, 1).Value = ""
Columns("P").EntireColumn.Hidden = ActiveCell.Offset(0, 2).Value = ""
Columns("Q").EntireColumn.Hidden = ActiveCell.Offset(0, 3).Value = ""
etc..
With the active cell being N28 (where I've put a button).

However, for the life of me I cannot make this work.

Can someone help or give feedback on what I'm doing wrong? I am new to relative references, it's obvious I'm not instructing excel correctly here.
Appreciate you time!


2017-05-11 11:43:24

Willy Vanhaelen

@Miriam
You can only have one event macro in a sheet so the first macro works and hides or unhides the columns depending on the value in D2.

Your second macro is not an event macro so it will only be executed if you explicitely run it.

I noticed that the two macro codes are identical except for the words Penguin and Elephant. So you can combine the two macros into one by replacing in the first macro:
If Range("D2").Value = "Penguin" Then
with:
If Range("D2").Value = "Penguin" Or Range("D2").Value = "Elephant" Then




2017-05-10 16:14:17

Miriam

Hi! I am successfully created one macro, however I have a few others that I need to create for the same sheet, and I am having difficulty getting it to work. Below are the two macros I wrote and information about the pages that they are on. I am not sure what I need to do in order to have the two macros both working. Thank you in advance!

The one that is working successfully is in a window called "File Name" - Sheet1 (Code) and what I wrote is:

Sub Penguin()
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("D2").Value = "Penguin" Then
Columns("E").EntireColumn.Hidden = True
Columns("F").EntireColumn.Hidden = True
Columns("G").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Else
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
End If
End Sub

My question is how to add another macro. I have tried adding it to the same sheet1 window as well as putting it in a Module, but neither makes it work. Below is what I wrote for the second macro:


Sub Elephant()
If Range("D2").Value = "Elephant" Then
Columns("E").EntireColumn.Hidden = True
Columns("F").EntireColumn.Hidden = True
Columns("G").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Else
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
End If
End Sub


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.


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.

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.