Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cleaning Up Named Ranges

    Hi Again:
    During the course of building this application, I ended up with numerous Named Ranges that I created and then decided not to use. Is there any way that I can identify those that are not used in formulae or hyperlinks, other than inspecting them one by one and searching the formulae in each sheet? Also, how could I pick up whether or not a particular named-range was being used in a VBA statement other than inspecting all such statements?
    Or should I simply leave them to continue in oblivion ad infinitum?
    Stephen

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Cleaning Up Named Ranges

    Hi Stephen

    You don't say which version of Excel you are using. Always helpful information.

    If you know enough about VBA you can automate this task:
    1) Get a list of all the names in your workbook. Make sure that you can distinguish Workbook level from worksheet level names.
    2) Loop through the list and find any occurrence of that name in all the items you have mentioned. If you do, you are using that particular name, skip it. If you don't find any reference, then guess what?! Delete it.

    I think it would be easier to look at the list in #1 and manually delete these names. I guess when I am working on a project, I get so wrapped up into it, I know everything about it.

    Let me know if you need more help.

    Wassim
    Your Cell Mate.
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cleaning Up Named Ranges

    Stephen, cannot think of anyway to automate the process in a feasible way. If you go through them manually by pressing F5 (Ctrl-G) and actually select each range and decide from memory whether it is used or not. If you find 2 names referring to the same location, I suppose you would have check which was in use (if not both !).

    Sorry that there is not an easier way, - unless of course somebody knows different.

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Cleaning Up Named Ranges

    A couple of suggestions:
    1) Don't forget the Paste list command. Make sure you are on an empty area of your spreadsheet. Go to Insert, Name, Paste Use the Paste list button. This will paste a list of the ranges in the spreadsheet. It isn't actively linked but it will give you an idea of the ranges in one step.
    2) Ctrl + ` will toggle to a view of all formula in the spreadsheet. You should be able to spot the cells using ranges in the formulas easily.
    3) Hidden under the Tools, Auditing Menu (in Excel 2K) is the Trace Precedents button. Select the cell with the formula and Click on the Trace Precedents button. Excel will draw a to the cells referenced by the formula. Great tool for learning about a new spreadsheet quickly!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning Up Named Ranges

    Stephen,

    Maybe you can write some code in which you go through all names you defined. To get a list of the names and addresses that refer to that name, you can use code similar to:

    <pre>Sub ListNames()
    Dim NM As Name
    Dim R As Range
    On Error Resume Next
    For Each NM In Names
    Set R = Nothing
    Set R = NM.RefersToRange
    If Not R Is Nothing Then
    MsgBox NM.Name & " = " & R.Address
    End If
    Next
    End Sub
    </pre>


    Once you have the list of names, you can use code like this to check if the name is used in a formula (here I assumed that one of the named ranges has the name "Test1"):

    <pre>Sub ListFormulas()
    Dim cell As Range
    Dim R As Range
    Set R = Range("A150")
    For Each cell In R
    If InStr(1, cell.Formula, "Test1") Then
    MsgBox "Name in use in cell " & cell.Address
    End If
    Next
    End Sub
    </pre>


    I don't know if this is what you are looking for or if it helps you any further ...

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning Up Named Ranges

    This isn't automated, but is it a way to find if a name is used. In the Edit menu select Go To, the select the name from the list and click OK. This should select the cell(s) that the name defines. Then in the Tools menu select Audit and Trace Dependants. This should draw an arrow to all of the cells that refer to the range defined by the name. You can then look at each dependant cell to see if it refers to the name or to the range directly.
    Legare Coleman

Posting Permissions

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