Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Denver, Colorado, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Defined Name range count (Excel 2000)

    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.

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Defined Name range count (Excel 2000)

    '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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Defined Name range count (Excel 2000)

    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

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined Name range count (Excel 2000)

    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 <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Jan 2003
    Location
    Denver, Colorado, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined Name range count (Excel 2000)

    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.

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Defined Name range count (Excel 2000)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined Name range count (Excel 2000)

    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.

  8. #8
    New Lounger
    Join Date
    Jan 2003
    Location
    Denver, Colorado, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined Name range count (Excel 2000)

    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!

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined Name range count (Excel 2000)

    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).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •