If you’ve discovered how useful Names can be in a spreadsheet, you may also know they can get-in-the-way-if-you-have-too-many. Just check out the Name Box in the picture to the right.
Each Name has a Visible property that’s set to TRUE by default. You can hide a Name by changing the Visible property to FALSE, but only in the VBA Editor.
Hide Names in Excel
If you have a great many names to hide, it’s best to loop through the Names collection and change the Visible property value to TRUE for all Names.
Sub HideAllNames()
Dim n As Name
For Each n In ThisWorkbook.Names
If n.Visible Then n.Visible = False
Next n
End Sub
After running this code, you now have a blank Name Box.
If you just want to hide a few Names, the code below, executed in the Immediate Window of the VBA Editor, will do the trick. Just change out “MachName” for each Name you want to hide.
Go To Hidden Names
You can still Go To a Name that is hidden, just type a Name in the Name Box and hit enter.
Or use the keyboard shortcut Ctrl+G or F5 to bring up the Go To dialog box, type a Name in the Reference text box, and click OK.
If the Name’s corresponding range address is on a hidden worksheet, this won’t work.
Show Names in Excel
While working on a file, it can sometimes be advantageous to actually SEE ALL the Names. The following VBA code will do just that.
Sub ShowAllNames()
Dim n As Name
For Each n In ThisWorkbook.Names
If n.Visible = False Then n.Visible = True
Next n
End Sub
Keep Some Names Visible with Code
Sometimes you may want to leave a few of the Names visible for users. In the code below I’m looping through the Names collection, setting the Visible property to False, then checking to see if any have “Lookup” in the Name property string and making those Visible.
Sub ShowSomeNames()
Dim n As Name
For Each n In ThisWorkbook.Names
n.Visible = False
If InStr(1, n.Name, "Lookup", vbTextCompare) > 0 Then
n.Visible = True
End If
Next n
End Sub
I use the VBA Function InStr, which stands for In String, to check and see if the Name Property of each Name has “Lookup” contained in it, by starting at character 1. If the InStr Function finds “Lookup” with in the text, it returns a numerical value, which represents the character position of where “Lookup” starts within the text string. If the InStr function doesn’t find “Lookup” within the Name Property, it returns a zero (0).
In the code above, if the InStr function finds “Lookup” within the Name Property string, the code sets the Visible property to TRUE. Here’s what shows in the Name Box after running this code.
You can open up the Excel VBA Editor, create a new module, copy any of the first two code snippets, paste into the module, and it should run just fine. The third snippet is more specialized and would need modification of the “Lookup” attribute for it to work.
My apologies for not indenting the code above, but I’m still learning HTML and CSS.