Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting range names (Excel 2003)

    I have a workbook that somewhere along the line picked up over 2,000 range names which now show #REF in the definition. They make it impossible to find my valid range names. ASAP has a function to delete all range names in a workbook, but I don't want to lose my good ones. Is there a way to get rid of just those range names with bad references? If not, s there a macro that could be written to do this? TYIA

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting range names (Excel 2003)

    I picked up these tips somewhere a long time ago. Please note the macro will prompt to confirm delete. I dunno if this is the best scenario for over 2000 names??!!

    PRINT A LIST THEN REMOVE:
    Using the menu again go to Insert | Names | Paste. This enables you to look at all the defined names and their references at once on a worksheet, and print it if you like. Still, you will need to go back and delete the invalid names manually, but this sometimes makes them easier to find.

    USE A QUICK VBA PROCEDURE:
    This is the semi-automatic way. You still will probably want to confirm deletions, but we can use a subroutine procedure to loop through the Names collection quickly and determine whether to keep or eliminate the names stored in your workbook. This is certainly the most effective way, because you can be assured that all names, including any hidden ones, will be evaluated.

    <pre>Sub DisplayNames()
    Dim n As Name
    ' Loop through the Names collection in this workbook
    For Each n In ThisWorkbook.Names
    With n
    ' Display a messagebox providing the user with an opportunity to
    ' delete an offending link
    If MsgBox("Delete " & .Name & " ? " & vbCrLf & _
    "Refers to " & .RefersTo, vbYesNo + vbQuestion) = vbYes Then _
    .Delete
    End With
    Next
    End Sub
    </pre>

    Having spaces in sheet names could cause run time errors in this subroutine procedure.
    Regards,
    Rudi

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting range names (Excel 2003)

    Are sheetnames = tabnames? If so, that's a problem. Many of my sheets do. What should you do with that "runtime error"? Also, the code does not find the reference errors that are the only names I'm trying to get at. Thanks for the idea.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting range names (Excel 2003)

    OK, here is a macro to do the job.

    Sorry, after looking at the macros in my previous reply...it seems a little out of context. This one I tested and it works like a charm to get rid of ONLY the #Ref name range errors.

    Sub DeleteErrorRangeNames()

    For Each nr In ActiveWorkbook.Names
    If nr.RefersTo Like "*REF!*" Then
    nr.Delete
    End If
    Next nr

    End Sub
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting range names (Excel 2003)

    Jan Karel Pieterse's excellent free Name Manager add-in has an option to display all names with errors; you can then easily select and delete them.
    Attached Images Attached Images
    • File Type: png x.png (16.1 KB, 0 views)

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting range names (Excel 2003)

    Great ideas-will try both! THanks.

Posting Permissions

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