Checking reference for named cells

[Kevin]

If I have several named cells in say 10 worksheets, and I delete sheet 8,
then I have some named ranged left out there with no valid reference.

How can I check the validity of a named cell in vba? I have this so far...

Sub test()
Dim Nam As Name
For Each Nam In ActiveWorkbook.Names

"Place code here to check if reference on name is good, if it isnt then
delete that name."

Next Nam
End Sub

Thanks in advance

 

[Bernie Deitrick]

Kevin,

Excel will automatically delete the name - it is the dependent cells formulas that will error out.
Try the macro below to find the #REF! errors.

HTH,
Bernie
MS Excel MVP

Sub FindMissingNameReferences()

Dim mySht As Worksheet
Dim myCell As Range
Dim err As String

For Each mySht In ActiveWorkbook.Worksheets
On Error GoTo NoErrs
For Each myCell In mySht.Cells.SpecialCells(xlCellTypeFormulas, 16)
err = CStr(myCell.Value)
If err = "Error 2023" Then
MsgBox myCell.Address(, , , True) & _
" has a reference to a missing name."
End If
Next myCell
NoErrs:
Resume nextSheet
nextSheet:

Next mySht
End Sub

 

[Joel]

For Each Nam In ActiveWorkbook.Names
If Left(Nam.Value, 5) = "=#REF" Then
'put your code here
End If
Next Nam

 

[Kevin]

thanks Joel, that is exactly what I came up with while poking around..

If Left(Nam, 5) = "=#REF" Then
Nam.Delete
End If

Bernie, I would think that if you deleted a sheet that the names would be
deleted also, but when I go to INSERT-NAME-DEFINE, they are still listed jst
with bad references. This was making other macros I had mess up as it was
still going through old names and giving me bogus values..

This will work for though... Thanks guys!!!

 

[Bernie Deitrick]

Sorry - I mis-read your post, and was thinking about the error raised when a block of cells
containing a named range is deleted.

Bernie
MS Excel MVP