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: Unhiding a Single Column.

Unhiding a Single Column

by Allen Wyatt
(last updated September 1, 2016)

4

I regularly hide and unhide columns in my worksheets. If I have an entire range of columns hidden, I find it a bother to unhide a single column out of all those hidden. For instance, if I hide columns C:M, and I want to unhide column F, then I need to unhide the entire range and then rehide C:E and G:M. (There are other ways I could accomplish the same task, but none of them are particularly endearing.)

However, I find that a single column can be unhidden very easily using a macro. Consider the following:

Sub UnhideSingleColumn()
    Dim Col As String
    Dim rng As Range

StartHere:
    Col = InputBox("Enter column to unhide.", "Unhide Column")
    If Col = "" Then Exit Sub
    On Error Resume Next
    ' if not a valid range, an error occurs
    Set rng = ActiveSheet.Columns(Col)
    If Err.Number <> 0 Then
        On Error GoTo 0
        Err.Clear
        MsgBox "Invalid input! Please input a valid column."
        GoTo StartHere
    End If

    rng.EntireColumn.Hidden = False

    MsgBox "Column " & UCase(Col) & " is now visible.", _
      vbOKOnly, "Unhide Specified Column"
    Set rng = Nothing
    On Error GoTo 0
End Sub

The macro prompts the user for which column should be made visible, and then tries to select that column. If the column cannot be selected, then an error is generated and the user is again asked for input. If the column can be selected, then its .Hidden property is turned off, thereby making the column visible.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2405) 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: Unhiding a Single Column.

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

Changing the Footnote Separator

When you print a document that uses footnotes, Word normally places a small line between the end of the document body text ...

Discover More

Printing a Macro List

Need a list of all the macros you've created? Word doesn't provide a way to create such a list, but you can use the ...

Discover More

Quickly Changing Tab Alignment

Need to change the alignment of some tabs in a multitude of paragraphs? How you approach the problem depends on whether those ...

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 (menu)

Countering Compressed Columns

If you open a workbook and find that the width of some of your columns has been changed, the discovery can be frustrating. ...

Discover More

Hiding Columns Not within a Date Range

Want to automatically hide some columns that don't meet a date criteria that you set? You can't do it automatically, but you ...

Discover More

Changing Column Width

When creating a worksheet, you often need to adjust the width of columns so your data looks great. Here's how to quickly and ...

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 seven minus 1?

2012-07-09 02:58:31

g.subramania sarma

I want to hide a row based on the reply to a question which is appearing in a cell

For Eg, if the reply to cell A7 is "Yes",
the rows 8 to 10 should hide

Is it possible and if so how

Advance thanks

G.S.Sarma


2012-03-19 21:49:22

Hemant Vyas

Using Hans method till jump to cell address and then just have a key activated macro to unhide column may be speedier.


2012-03-19 10:25:37

David Bonin

Using the above example, I would type a cell address for the desired column in the name box (the little field immediately above "A" designating column A) and then unhide the column using the familiar menu Format > Column > Unhide. Any address in the column will do, eg: F1 or F872. I can use ranges, too. While this may not be quite as short as a macro, it uses familiar steps to do an uncommon job -- always a good thing.


2012-03-19 02:02:03

Hans Kiesewetter

I use an other method, no macro.
Use F5/Goto and enter a cell adress to jump to a cell in the hidden column. Then click format/column/unhide


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.