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


"Kevin" wrote in message
...
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