how to delete "NOT VALID" range names by vba

Hi,

I have to work sometimes with files that for some reason have a huge number of rangenames that refer to workbooks no longer there, and not only that, the range name itsef contains japanese characters, some of the references are dead etc.

since these range names are useless (and sometimes do not allow to change to A1 reference mode) I want to delete them, however the normal sub to kill them (such as these do not delete these invalid names

any workarounds?

otherwise I'll have to delete them one by one, which works flawlessly, and endlessly...

thanks!

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

Many thanks Whizbang I knew about the REF solution but was looking for something like this as many range names get mixed when working with different workbooks

I just updated my most recent code sample. I realized there was a potential that it could try to delete the same named range twice (if the named range referenced another workbook, but also had #REF in the address). So, I changed it to an Elseif scenario.

Originally Posted by Whizbang

I just updated my most recent code sample. I realized there was a potential that it could try to delete the same named range twice (if the named range referenced another workbook, but also had #REF in the address). So, I changed it to an Elseif scenario.

Thanks again so much.

however my real problem is not so much to choose which range names to delete or not (would be happy to delete them all!), but rather than when the macro gets to the line rgnm.delete produces an error. also sometimes there are a number of hidden rangenames that do not appear even if I go through all range names and set rgnm.visible = true

still the problem is small enough (sort of) for the good old contingency plan of doing it by hand!


regards,

Bernat

Please explain and clarify the error you are receiving. What is the error you are getting? Are some names being deleted, or are none? Post a sample workbook if you can.

The code below will delete all named ranges:

Sub DeleteAllNames()

Dim nName As Name

    For Each nName In Names

       nName.Delete

    Next nName

End Sub