Tom’s Tutorials For Excel: Hiding Your Named Ranges
In the below picture, your workbook’s named ranges can be shown by clicking the down arrow next to the Name box.
The following macro hides the named ranges, and as the next picture shows, you can work with hidden named ranges the same as you would when they are visible.
Sub HideNamedRanges()
Dim nName As Name
For Each nName In Names
nName.Visible = False
' = True to reverse
Next nName
End Sub
Dear Tom
The macro hides the range names only from the name box, right? The range names would be visible at all other places (including the formula bar, as shown by you), right?
The range names are visible in the formula bar in the picture because they are part of a formula under ordinary circumstances. Just like any other situation when you don’t want the formula bar to show the formulas or cell contents, format the cells as Locked and Hidden, and protect the worksheet.