Tom’s Tutorials For Excel: Hiding Your Named Ranges

by Tom Urtis 2 Comments ↓

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

‹ Tom’s Tutorials For Excel: Displaying a Column’s Width

Tom’s Tutorials For Excel: Using a Ribbon Group’s Dialog Launcher ›

Posted in Tom's Tutorials for Excel
Tags: Formulas Functions, Macros Events Classes API

2 comments on “Tom’s Tutorials For Excel: Hiding Your Named Ranges”

1. sandeep kothari says:

   February 23, 2020 at 5:51 pm

   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?

   Reply
   • Tom Urtis says:

     February 23, 2020 at 7:12 pm

     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.

     Reply