Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Assigning a Macro to a Keyboard Combination
Hiding Rows Based on a Cell Value
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
Don't Go in Debt for Christmas! Tired of trying to keep up with the Joneses for Christmas? Want to enjoy the season rather than dread the aftermath? Learn how you can avoid the financial traps that spring up every Christmas.