Defined Name range count (Excel 2000)

Topic

New Reply

I’ve inherited quite a few large workbooks. The people who “owned” these before me were terrible with regards to naming ranges. They would create hundreds of them, use some of them, not others, make duplicates. Big mess.

My basic goal is to have a list of these Names in the workbook and then see where (and how often) they are being used. Thay way I can delete those that aren’t being used and generally clean up the workbook. As usual, I’m trying to avoid doing all of this manually.

I’ve tried two approaches:

1) Using the Range.Dependents.Count – Only counts those that are one the same worksheet as the where the named range is defined.
2) Use Find to search for the Defined Range name – I’ve tried it using Find, however there are instances where one name might be contained within another name. Unfortunately, I can’t control this. For example:

A1:F5 defined as “Name”
G2:H15 defined as “Name1”

If one cell has the formula “=sum(Name)” and another cell has the formula “=sum(Name1)”, using (and counting) with the Find function will return 2 for “Name”, because “Name” is contained within “Name1”.

It seems like there should be a way to count the # of times a defined name range is used in a workbook.

Reply | Quote

Replies

‘This should get you started…

‘Paste this in a module in the workbook in question, click inside the pasted text and press the F5 key.
‘Adds a new sheet to the active workbook.
‘Lists all Names in the workbook and what the name refers to.
‘———————————————————–
Sub FindAndListNames()
Dim NewSheet As Worksheet
Dim WBname As Name
Dim N As Long

N = 3
Set NewSheet = Worksheets.Add(after:=Sheets(Sheets.Count), Count:=1)
On Error Resume Next
NewSheet.Name = “Names List”
On Error GoTo 0
For Each WBname In ActiveWorkbook.Names
NewSheet.Cells(N, 1).Value = WBname.Name
NewSheet.Cells(N, 2).Value = “‘” & WBname.RefersTo
N = N + 1
Next ‘WBname
NewSheet.Columns(“A”).AutoFit
Set NewSheet = Nothing
End Sub
‘———————————————————–

Regards,

Jim Cone
San Francisco, CA

Reply | Quote

All this seems to do is do the same thing the builtin paste-list [insert -name – paste – pastelist] does (only yours create a new sheet)

Am I missing something. The poster was looking for dependents on a different sheet

Reply | Quote

You have just bumped into a very interesting addition to the wishlist of my name manager!
Download my Name manager from the Excel MVP page below my signature. It will help you with parts of your problem.

I’ll try to attach the latest version to this message. If it’s not there I failed .

Reply | Quote

Thanks for your thoughts. I tried the NameManager and it doesn’t have what I’m looking for. I’m already able to get a list of the names and what they refer to. What I’m trying to do is find out how many times in the workbook the names are being used.

Reply | Quote

Another suggestion:

Download Findlink.zip from the excel MVP page below.
It will search amost all objects in any workbook for a search string you provide and it thus able to look for defined names too.

It has a list option that provides you with a list of all places the search string is found.

Drawbacks:
– only one name at the time can be searched
– Fred will also find Freddy (but that can be checked in the listing you get).

I’m working on including an “unused names” category in the name manager, but it isn’t ready yet. This is currently only going to look in worksheet formulas, not in objects and other stuff (conditional formats, validation formulas, etcetera).

Reply | Quote

One other problem: be sure to check all of your code modules to see if they use the name before deleting it. I have the same problem, trying to fix workbooks written by others. The people in my company love to use Named Ranges in code. You’ll need to check all of the Range objects and all of the Names collections at a minimum. –Sam

Reply | Quote

juschuma, I see you are in very good hands here.

Can you settle for knowing that the name is used in a range for the external references, i.e. w/o the exact count of the range cells ? It would simplify the usage of the NavigateArrow method. As for the usage in code mentioned by SammyB, I will leave this to others, I always have troubles in programming the VBE.

Reply | Quote

I suppose the count isn’t the most important feature. If I know the range is used or not used, that would likely be enough.

CRI, thanks for sending me here and checking in!

Reply | Quote