Delete defined names with errors using VBA

[MikeM_work]

Using this code snippet from Microsoft (that I modified slightly), I can
delete any and all defined names without errors.
Any defined name that has a #REF! error, for example, cannot be deleted with
the VBA.

Is there any VBA code that will delete defined names with errors?

Thanks!

Mike


Sub DELETE_VISIBLE_Names()

' Dimension variables.
Dim xName As Variant
Dim Result As Variant
Dim Vis As Variant

' Loop once for each name in the workbook.
For Each xName In ActiveWorkbook.Names

'If a name is not visible (it is hidden)...
If xName.Visible = True Then
Vis = "Visible"
Else
Vis = "Hidden"
End If

If Vis = "Visible" Then xName.Delete

' Loop to the next name.
Next xName

End Sub

 

[Rick Rothstein]

Can you show us an example that does not delete? I just tried it with a
Defined Name that covered a range of COUNTIF formulas where the range part
of some of the COUNTIF statements were #REF! and I was able to delete that
Defined Name.