PDA

View Full Version : Named Ranges.!



V.B.A.02
09-04-2017, 08:54 AM
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
09-04-2017, 09:50 AM
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
09-04-2017, 10:27 AM
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
09-05-2017, 12:58 AM
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
09-05-2017, 05:53 AM
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. :dunno: 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
09-05-2017, 07:07 AM
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
09-05-2017, 07:51 AM
....
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 = Falsewill remove MySecretNamedRange from the list seen in the Name Manager.

Jan Karel Pieterse
09-05-2017, 08:08 AM
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 (http://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.