Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. 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: Unhiding a Single Column.
by Allen Wyatt
(last updated April 1, 2017)
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, I may hide columns C:M and want to later unhide column F. There are a number of ways this can be done:
There are a few other ways to unhide the column, as well, but (as I said) I find such approaches a bother. Unhiding either a single column or a range of columns is very easy to do through a spiffy little 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 (11471) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Unhiding a Single Column.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
If you open a workbook and find that the width of some of your columns has been changed, the discovery can be ...Discover More
If you were trying to format a worksheet and nothing you did could make the first two columns appear, would you be ...Discover More
One way you can widen the columns in a worksheet to fit whatever is in the column is by double-clicking the right edge of ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.