Microsoft Office Tutorials and References
In Depth Information
Finding and Deleting Erroneously Named Ranges
Case vbYes¶
DisplayList = True¶
'Create a new workbook with one sheet¶
Set Book = Workbooks.Add(xlWorksheet)¶
With Book.Worksheets(1).Range("A1:B1")¶
.Value = Array("Name", "RefersTo")¶
.Font.Bold = True¶
End With¶
Case vbNo¶
DisplayList = False¶
Case vbCancel¶
GoTo exiting¶
End Select¶
For Each Nm In ActBook.Names¶
'Does the name include a #REF error ?¶
If InStr(1, Nm.RefersTo, "#REF!") + InStr(1, Nm.RefersTo, _¶
"#¡REF!") > 0 Then¶
If DisplayList Then¶
'Add it to the list¶
With Book.Worksheets(1)¶
With .Cells(.Rows.Count, 1).End(xlUp).Offset(1)¶
.Value = Nm.Name¶
.Offset(, 1).Value = "'" & Nm.RefersTo¶
End With¶
End With¶
Else¶
Application.DisplayAlerts = False¶
Nm.Delete¶
Application.DisplayAlerts = True¶
End If¶
End If¶
Next Nm¶
exiting:¶
'Restore screen updating¶
Application.ScreenUpdating = True¶
End Sub¶
Exl
Search JabSto ::




Custom Search