ExcelTips (Menu Interface)
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 site focusing on the ribbon interface.
ExcelTips FAQ
Ask an Excel Question
Make a Comment
Free Business Forms
Free Calendars
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 versions: 97 2000 2002 2003 2007
Related Tips:
Save Time! ExcelTips has been published weekly since late 1998. Past issues of ExcelTips are available in convenient ExcelTips archives. Have your own enhanced archive of ExcelTips at your fingertips, available to use at any time! Check out ExcelTips Archives today!