Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier 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 28, 2015)

19

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 (9730) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel 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

Copying Formulas using a Pattern

Copying formulas from one cell to another is quite intuitive in Excel—unless you want the copied formulas to follow ...

Discover More

Suppressing ASK Fields When Printing

Do you like using ASK Fields in your documents to get information from the user but don't want Word to update the fields more ...

Discover More

Reversing Type

Most text appears black on white, not white on black. If you want to change this so that your type is reversed, here's a ...

Discover More

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!

More ExcelTips (ribbon)

Shortcuts to Hide Columns

Need a quick way to hide and unhide columns in a worksheet? The shortcuts described in this tip can help fill the bill.

Discover More

Unhiding a Single Column

In a worksheet with lots of hidden columns it is a real pain to try to unhide just one or two columns. The best solution is ...

Discover More

Unhiding Columns that are Persistently Hidden

If you were trying to format a worksheet and nothing you did could make the first two columns appear, would you be ...

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. 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 three minus 2?

2016-02-05 11:19:39

Jonas Hammer

I want to hide an entire row IF a cell is TRUE and IF a specific cell in the soon-to-be-hidden-row is TRUE as well, otherwise it should show.

Really hope that you can help me!
Thank you!


2016-01-05 20:59:22

Dorocida

I'd like to create the following functionality in excel:

When I put my cursor in any cell of column “B” I would like the following steps to be taken:
• Selected Blank Cell Action to take: Group columns C-F
• Selected Cell Value is YES Action to take: Ungroup Columns C-F
• Selected Cell Value NO Action to take: Group C-F
If no cell is selected in column B Action to take Group Columns C-F

Sincerely,
New Learner


2015-12-14 16:20:45

Jordan

Hi, how can I make it possible to hide column ranges based on selections in mulitple cells?

For example, if A2 says "no", I want range B;B1 hidden.

But if B2 says "1", I want range Q:B1 hidden. And if B2 says "2", I want range AF:BI hidden, and so on.

This will not work with this Macro set-up, I assume because more than one cell is referencing the same range of columns. Would really appreciate the help!

Thanks,
Jordan


2015-12-09 13:12:03

Daniel

How would I make this applicable for multiple if statements and values? For example, if value = 1, hide o:bj columns, if value = 2 hide aa:bj columns, if value = 3 hide am:bj column, etc.

Thanks


2015-07-23 10:28:00

Andrew

I'm hoping that someone can help me with my question.

I stumbled upon this tip looking for a means to hide/unhide successive columns based on whether or not there are contents in a previous column, and also for a range of rows, for the purpose of creating a hazard assessment.

For example, in a 3-step assessment, if there is one hazard identified in any of the steps (written in column A), they choose it from a drop down in column B. This would then need to unhide column C, to allow another hazard to be selected, if necessary. And there is room for 10 hazards total.

Ultimately, I'm trying to cut out unnecessary blank cells at the beginning of the assessment process.

Unfortunately, I have absolutely zero background with VBE and would be relying on total copy-paste help, if possible.


2015-07-07 03:24:15

Brad

how to Add macro to hide rows in a spreadsheet where the values in a column is 0


2015-05-04 11:19:41

Gary Lundblad

Thank you for your assistance. Willy, I read your second post first, and switched the order of the row and column, and it worked. I didn't try the first solution you offered, because the other one one worked.

Thank you!

Gary


2015-05-01 05:54:01

Willy Vanhaelen

@Gary Lundblad

The proper syntax of Cells() is:

Cells(rownumber,columnnumber)

You reversed it.


2015-05-01 05:23:14

Willy Vanhaelen

@Emma

replace
Range("D:E").EntireColumn.Hidden = Range("G2") = 1

with
Range("D:E").EntireColumn.Hidden = (range("G2") = 1 Or range("G2") = 5)


2015-04-30 11:20:14

Gary Lundblad

I am trying to create a macro that will hide columns based on the value in row 4. True means hide the column and False means don't hide it. For some reason the code isn't working and I'm not sure why. The code I'm using is below.

Sub Collapse()
BeginColumn = 6
EndColumn = 50
ChkRow = 4

Application.ScreenUpdating = False

For ColumnCnt = BeginColumn To EndColumn
If Cells(ColumnCnt, ChkRow).Value = True Then
Cells(ColumnCnt, ChkRow).EntireColumn.Hidden = True
Else
Cells(ColumnCnt, ChkRow).EntireColumn.Hidden = False
End If
Next ColumnCnt
Application.ScreenUpdating = True

End Sub

Thank you!

Gary


2015-04-29 05:59:30

emma

@Willy Vanhaelen
your tip form 21.03.2015 has worked for me, however, if I wanted to hide the column based on more than one criteria how would I add this in. Using your previous scenario I would want to hide the column if the value of G2 is 1 or 5. I'm not sure how to show the 'or 5' bit. Thanks


2015-04-17 11:48:03

Seorsa

Is it safe to assume that this same code will work for rows, substituting "row" or "rows" where appropriate?


2015-03-29 08:44:37

Willy Vanhaelen

@David Empsall
If the macro in my comment of 21 March doesn't work, you probably didn't apply my warning in the final note.

To hide non contiguous columns can be achieved by replacing: Range("D:E")
by for example: Union(Columns("B"), Columns("D"), Columns("F"))

Being clear on expressing what you want is often the key to the solution.
What exactly do you mean by 'based on the value of a cell being between 1 and 10' ?
- Do you enter the numbers 1 to 10? What should happen for each number?
- Is it a formula that yields 1 to 10? In this case the technique of this tip is unreliable and should not be used .


2015-03-28 08:18:52

David Empsall

@Willy Vanhaelen
I'm a novice, but I just cannot get the small trigger routine to work at all.
The routine in the main body above works but, as you rightly say, the worksheet is always refreshing itself whether or not the key cell is changed.

I would like to hide a lot of columns (not contiguous ones) based on the valye of a cell being between 1 and 10.


2015-03-23 13:54:05

Scott

Thanks.


2015-03-21 09:23:19

Willy Vanhaelen

Although it's basically a nice tip, it appears to be badly worked out referring to the 28-02-2015 comments of 'Rod Grealish' and 'Helyosman'. But there is even more.

It's a bad choice to use the Worksheet_SelectionChange event in the second macro of this tip. The position of the cellpointer is not relevant but the value of cell B4 is. This can be monitored by the Worksheet_Change event. Then you can check if B4 has been changed and only then hide or unhide column H.

@Tony M:

The following simplified macro will solve your problem:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$2" Then
Range("D:E").EntireColumn.Hidden = Range("G2") = 1
End If
End Sub

Note that this macro should not be placed in a module but in the sheet's code page. Right click the sheet tab and select 'View Code'. That's the place to be :-).


2015-03-20 16:19:52

Tony M

My expectation was that when a user chooses a prompt on a cell on the worksheet, that the columns would hide/unhide based on the one cell. The real time macro does not appear to work, or I'm missing something. I copied it and made my adjustments. Please advise.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("G2").Value = 1 Then
Columns("D").EntireColumn.Hidden = True
Columns("E").EntireColumn.Hidden = True

Else
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False

End If
End Sub


2015-02-28 09:56:54

Helyosman

Hello and thanks,

what's more for the macro called "Sub HideColumn2()" you missed a parenthesis at the line 6.


And (IsNumeric(rCell))

Have a great day.


2015-02-28 06:31:57

Rod Grealish

In the sub HideColumn2() line continuations (a space followed by the underscore character) are needed at the end of lines 1 and 2 of the If statement as follows

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

The If statement and the line before it can be replaced by a direct assignment to Columns("H").EntireColumn.Hidden viz.

Columns("H").EntireColumn.Hidden = (Not IsEmpty(rCell)) _
And (IsNumeric(rCell) _
And (rCell.Value = 0)

The same applies to HideColumn1()

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


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu 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.