Hidden Named Ranges


Application.ActiveWorkbook.Names.Add Name:="MyNamedRange1", _ 
                                     RefersTo:="Sheet1!$A$1:$B$8"
                                     Visible:=False

Application.Names.Add Name:="MyNamedRange1", _ 
                      RefersTo:="Sheet1!$A$1:$B$8"
                      Visible:=False

Since Application is the default member you could abbreviate it and use:

ActiveWorkbook.Names.Add Name:="MyNamedRange1", _ 
                               RefersTo:="Sheet1!$A$1:$B$8"
                               Visible:=False

Names.Add Name:="MyNamedRange1", _ 
               RefersTo:="Sheet1!$A$1:$B$8"
               Visible:=False

Show Hidden Named Ranges

This subroutine will display any hidden named ranges so they can be viewed in the Name Manager dialog box.

Public Sub HiddenToVisible() 
Dim nameRge As Excel.Name
Dim lcount As Long
   For Each nameRge In ActiveWorkbook.Names
      If (nameRge.Visible = False) Then
         nameRge.Visible = True
         Application.StatusBar = "Visible - " & nameRge.Name
         lcount = lcount + 1
      End If
   Next nameRge
   Application.StatusBar = False
   Call MsgBox("'" & lcount - 1 & "' hidden named ranges are now visible")
End Sub


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext