Checking reference for named cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin - 28 Nov 2007 17:41 GMT

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

Reply to this Message

Bernie Deitrick - 28 Nov 2007 18:28 GMT

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

> 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.
[quoted text clipped - 12 lines]
>
> Thanks in advance

Reply to this Message

Joel - 28 Nov 2007 18:36 GMT

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

Reply to this Message

Kevin - 28 Nov 2007 18:53 GMT

:) 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!!!

> For Each Nam In ActiveWorkbook.Names
> If Left(Nam.Value, 5) = "=#REF" Then
[quoted text clipped - 18 lines]
> >
> > Thanks in advance

Reply to this Message

Bernie Deitrick - 28 Nov 2007 19:01 GMT

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

> 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..

Reply to this Message