I'm in need of your help in modifying a vba code that will delete any names in a workbook that meet the following criteria:
The "Refers to" is currently #REF! - For this step, I already have the code that works perfectly, see below
Dim nName As Name
Dim iRes As Integer
Dim strPrompt, strTitle As String
strPrompt = "This will delete ALL named ranges with reference error #REF!" & vbNewLine & vbNewLine & "Are you sure?"
strTitle = "Caution"
iRes = MsgBox(strPrompt, vbYesNo + vbCritical, strTitle)
.DisplayAlerts = False
.ScreenUpdating = False
If iRes = vbYes Then
For Each nName In ActiveWorkbook.Names
If InStr(1, nName.RefersTo, "#REF!") > 0 Then
.DisplayAlerts = True
.ScreenUpdating = True
In addition to the Step 1 above, I would also like the code to delete any names that are no longer used. I'm thinking that the code could scan names that have a reference to any worksheet in the workbook, leave these intact and delete all other names that refer to a hard-coded amounts, external files, url links, etc.
Any help with this will be greatly appreciated.