Named Ranges.!

[V.B.A.02]

Hello,

I am trying to remove all unused named ranges from a workbook.This workbook is very large as it contains a number of sheets (more than 50). It has many named ranges too which I can see now in Name Manager also Many named ranges have been deleted in past which I can not see in Name Manager.

Surprising thing is that:
In Name Manager there would be something 80 Named ranges, but when I count them using "Names.Count" count is more than what I see in name manager.

Can anybody tell me why it is happening and what could be the best way to know If a named range is used somewhere in the workbook and then delete all unused named ranges?

Help is appreciated.! Thanks

[SamT]

Names.Count includes BuiltIn names

Try Excel Menu >> Insert >> Name >> Paste >> Paste List

This will delete names with a RefersTo error

Option Explicit

Sub DeleteNamesWithError()
Dim Nm As Name
For Each Nm In ThisWorkbook.Names
If InStr(Nm.RefersTo, "#REF!") <> 0 Then _
Names(Nm.Name).Delete
Next
End Sub

[SamT]

the best way to know If a named range is used somewhere in the workbook and then delete all unused named ranges?

For each Name, For each sheet, Find, in formulas, that name
If Found Is Nothing, then the name is not used in that workbook




Note that a Name in one book can refer to a Name in a different book =Book3!AName or =[Book3]!AName. Depends on which workbooks are Open ATT.

First go thru all workbooks that reference this one and

Option Explicit

Sub NamesReferringToThisWorkbook()
Dim Nm As Name
Dim RfrsTo as string

For Each Nm In ActiveWorkbook.Names
If InStr(Nm.RefersTo, ThisWorkbook.Name) <> 0 Then
RfrsTo = Split(Nm.RefersTo, "!")(1)
MsgBox Nm.Name & " Refers to this book"
MsgBox "Refers to Range " & RfrsTo
End If
Next
End Sub

Make a list of Names in those books that refer to Named Ranges in this book.

[V.B.A.02]

Sam,

Thanks for your reply however if "Names.Count includes BuiltIn names", then why dont i see any name in a blank saved workbook, with blank saved workbook "Names.count" is 0.

[SamT]

They may not be present, but if they are they are not visible. They are generated and used by the Application.

Are you using any custom Themes? They probably have a Name. : We're getting into territory I haven't looked at.

Try these. Let me know what happens,

Option Explicit

Sub InvisibleNames()
Dim Nm As Name
Dim i As Long

For Each Nm In Names
If Nm.Visible = False Then
i = i + 1
MsgBox Nm.Name
End If
Next

MsgBox i
End Sub

Run this once, paste a list of names, or use NameManager, then run it again to restore the Names to normal

Sub SwapNameVisibility()
Dim Nm As Name

For Each Nm In Names
Nm.Visible = Not (Nm.Visible)
Next

End Sub

[Paul_Hossler]

Hidden (.Visible = False) names can be added by macros, add-ins, and from what I've read by the Solver.

Copying sheets between workbooks oft times will generate the 'Name already exists - use or change ...' message if both workbooks have used the same macro or add-in

The Document Inspector will flag their existence, but can't delete them


It's easy for a macro to add a hidden name, and there's a MS macro to delete

Thanks for your reply however if "Names.Count includes BuiltIn names", then why dont i see any name in a blank saved workbook, with blank saved workbook "Names.count" is 0.

They really aren't 'built-in'. There's no hidden names in a new WB since the macro or add-in that generates them hasn't been run on the WB .... yet

Option Explicit

Sub AddedHiddenNames()
    ThisWorkbook.Names.Add "Visible_01", "=A1", True
    ThisWorkbook.Names.Add "Hidden_01", "=A1", False
    ThisWorkbook.Names.Add "Visible_02", "=A1", True
    ThisWorkbook.Names.Add "Hidden_02", "=A1", False
 

End Sub

'https://support.microsoft.com/en-us/help/119826/macro-to-remove-hidden-names-in-active-workbook
' Module to remove all hidden names on active workbook
Sub Remove_Hidden_Names()
       ' Dimension variables.
       Dim xName As Variant
       Dim Result As Variant
       Dim Vis As Variant
       ' Loop once for each name in the workbook.
       For Each xName In ActiveWorkbook.Names
           'If a name is not visible (it is hidden)...
           If xName.Visible = True Then
               Vis = "Visible"
           Else
               Vis = "Hidden"
           End If
           ' ...ask whether or not to delete the name.
           Result = MsgBox(prompt:="Delete " & Vis & " Name " & _
               Chr(10) & xName.Name & "?" & Chr(10) & _
               "Which refers to: " & Chr(10) & xName.RefersTo, _
               Buttons:=vbYesNo)
           ' If the result is true, then delete the name.
           If Result = vbYes Then xName.Delete
           ' Loop to the next name.
       Next xName
   End Sub

[mikerickson]

....
In Name Manager there would be something 80 Named ranges, but when I count them using "Names.Count" count is more than what I see in name manager.
...

The code

ThisWorkbook.Names("MySecretNamedRange").Visible = False

will remove MySecretNamedRange from the list seen in the Name Manager.

[Jan Karel Pieterse]

Best to download my Name Manager and be in full control. It has a checkbox whether or not to display system names and a whole bunch of other filters you will appreciate I'm sure. www.jkp-ads.com/officemarketplacenm-en.asp It also has an unused names checkbox which is quite thorough and accurate as it avoids false positives like thinking Name1 is in a formula like =Name12*Name123.