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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Want to hide a column so it doesn't appear in the worksheet? It's easy to do using the formatting capabilities of Excel.Discover More
Want to automatically hide some columns that don't meet a date criteria that you set? You can't do it automatically, but ...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
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.