Hidden named ranges can be tricky to find and work with. This article will guide you through how you can show named ranges that were hidden using VBA.

How to show named ranges that were hidden

Named ranges are objects in a workbook's Names collection and they have a property called Visible. The Visible property determines the visibility status of a worksheet as its name refers. This property can take a Boolean value: TRUE or FALSE

True and False values specifies visible and hidden statuses respectively. The hidden named ranges are useful when you need source of the named range kept. The named ranges can be made visible or hidden only by VBA or 3rd-party add-ins.

You can use codes in two ways:

  • Module
  • Immediate Window

In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.

 

Unhide all named ranges

Module Version:

Sub UnhideAllNames()

 

    Dim nm As Name

 

    For Each nm In ActiveWorkbook.Names

 

        nm.Visible = True

 

    Next

 

End Sub

 

Immediate Window version:

For Each nm In ActiveWorkbook.Names: nm.Visible = True: Next

 

Unhide a specific named range

Module Version:

Sub UnhideSpecificName()

 

    ActiveWorkbook.Names("MyName").Visible = True

 

End Sub

 

Immediate Window version:

ActiveWorkbook.Names("MyName").Visible = True