Delete Named Range if no longer valid

[nigelk]

Hi all,

I have this code

  On Error Resume Next
    For Each Nm In ThisWorkbook.Names
        Nm.Delete
    Next Nm
    On Error GoTo 0

Is it possible to change it so that it only removes the names that evaluate to a Ref# error?

Thanks,Nigel

 

[Richard Schollar]

Hi Nigel

Try this:

  On Error Resume Next
    For Each Nm In ThisWorkbook.Names
        If Instr(1,Nm.RefersTo,"#REF")>0 Then Nm.Delete
    Next Nm
    On Error GoTo 0

 

[nigelk]

Thank you Richard, That works a treat.

Nigel

 

[philrv]

Tried it but it didn't work.Ran the code and it didn't delete them. Thanks much for the Input.

 

[SydneyGeek]

Richard's code should do the trick for that. I use it all the time to clean out redundant names.

So...
What did you do?
What happened?
What didn't happen?

Denis

 

[philrv]

Hi Sydney. I typed the code as stated and ran it from VB editor via the run command. The code ran but the defined names with the ref errors are still there in the name manager. I can't delete them thru that because the edit and delete buttons are not enabled.

 

[SydneyGeek]

Is the worksheet protected?

Denis

 

[philrv]

There are some sheets in the worksheet that are and some that are not. Would that make a difference? The ranges are not valid anymore since I deleted the sheets they were assigned to a while ago (like a Rookie,eh).

 

[SydneyGeek]

It might make a difference. Check to see which sheets those invalid names refer to.
If the protected ones are the problem you will need to unprotect, clean the names, then reprotect.

Denis

 

[SydneyGeek]

BTW, if you want to get a list of names do this:
New worksheet
F3
Alt+L

Denis