Auto hide column in excel

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.

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

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

MORE FROM ALLEN

Using Data Forms

Lots of people prefer to enter information directly into Excel, but there is another way that may be helpful: Using data ...

Discover More

Two Types of Page Numbers in a TOC

Word, when creating a table of contents, should automatically make sure that the page numbers it shows correspond to the ...

Discover More

Ignore Setting on Misspelled Words not Persistent

When Word flags a word as misspelled, you have some options of how to handle it. This tip explains those options and ...

Discover More

If you want to hide columns based on a cell value in Excel, there are a few different ways you can do this. One way is to use the conditional formatting feature. This will allow you to set up a rule that will hide the column if the cell value meets certain criteria. Another way is to use a macro. This will require some VBA code, but it can be done. Finally, you can also use a filter. This is probably the easiest method, but it only works if your data is in a table.

Conditional Formatting

To use conditional formatting, select the cells that you want to apply the rule to. Then, go to the Home tab and click on Conditional Formatting in the Styles group. Select New Rule from the drop-down menu. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format. In the Formula box, enter the following formula:

=CELL("col")=3

This formula will hide column 3 if the cell value is TRUE. You can change the 3 to any column number that you want. If you want to hide multiple columns, you can use OR in the formula like this:

=OR(CELL("col")=3,CELL("col")=4)

This will hide columns 3 and 4 if the cell value is TRUE.

Macro

If you want to hide columns based on a cell value using a macro, you will need to use some VBA code. The first thing you need to do is add a reference to the Microsoft Excel Object Library. To do this, go to Tools > References and select Microsoft Excel Object Library from the list. Then, insert a new module and paste in the following code:

Sub HideColumns()
Dim rng As Range
Set rng = Range("A1:A10")
For Each cell In rng.
If cell.Value = "TRUE" Then
cell.EntireColumn.Hidden = True
End If
Next cell
End Sub

How do you automatically hide columns in Excel based on cell value?

How do you auto hide in Excel?

How do I automatically hide and unhide rows in Excel based on cell value?