how to delete "NOT VALID" range names by vba

Originally Posted by Whizbang

If the named range is referring to another workbook, the linked code will not work. This is because the linked code is looking for named range with #REF! in the address. #REF! only appears, as far as I know, if the named range references cells within the same workbook. This is because when these cells are deleted, Excel tries to update the named ranges, and cannot. But, if the named range refers to other workbooks, the current workbook has no way to determine if the cells exist in the other workbook, or even if the workbook exists at all.

The only thing I can think of would be to add to the linked code a script that searches for the file, if the named range refers to another workbook, and if it does not exist, it deletes the named range. This will not handle events where the workbook does still exist, but that the cells referenced have been moved or deleted. This is because A1 (or whatever cell referenced) will always be there, even if the original A1 referenced was deleted or moved.

So, in short, I do not think you will ever find a perfect solution that does not include human interaction.

Here is the linked code modified to delete named ranges that refer to workbooks that do not exist:

Sub DeleteDeadNames()

Dim nName As Name
Dim strFullPath As String

    For Each nName In Names

        If InStr(1, nName.RefersTo, "#REF!") > 0 Then

            nName.Delete

        End If
        
        If InStr(1, nName.RefersTo, "[") Then
        
            strFullPath = Replace(Mid(nName.RefersTo, 3, InStr(1, nName.RefersTo, "]") - 3), "[", "")
            
            If Dir(strFullPath, vbDirectory) = vbNullString Then
                nName.Delete
            End If
        
        End If

    Next nName

End Sub

Wow, thanks a lot, I'll keep it in mind!
I admit at the end I followed the good old way of clicking on a range name and then on delete very fast --- I just hope I do not come across to many files with the problem !

regards

Bernat