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

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.

--
Rick (MVP - Excel)


"MikeM_work" <(E-Mail Removed)> wrote in message
news:3D44AD90-3E66-4CE4-AE7E-(E-Mail Removed)...
> 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