Named Ranges

[Miya]

Hi, in my name manger i have so many named ranges which have #REF, i dont know where they appeared from, because the other day i only had the relevent names, so why have i got ranges that refer to other workbooks, there are so many of them, how can get rid of them?

 

[VoG]

Try running this with a copy of your workbook:

Sub DeleteBadNames()
Dim nm  As Variant
Dim vTest As Variant
For Each nm In ActiveWorkbook.Names
    vTest = Empty
    On Error Resume Next
    vTest = Application.Evaluate(nm.RefersTo)
    On Error GoTo 0
    If TypeName(vTest) = "Error" Then nm.Delete
Next nm
End Sub

 

[Miya]

Try running this with a copy of your workbook:

Sub DeleteBadNames()
Dim nm  As Variant
Dim vTest As Variant
For Each nm In ActiveWorkbook.Names
    vTest = Empty
    On Error Resume Next
    vTest = Application.Evaluate(nm.RefersTo)
    On Error GoTo 0
    If TypeName(vTest) = "Error" Then nm.Delete
Next nm
End Sub

Thanks Vog