Delete defined names with errors using VBA

M

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
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top