range name cleaner out there? I have range names that cannot only be deleted by hand?!

Hi,

I work sometimes with files that with sheets copied from old excel files from japan and are loaded with Excel 4.0 macros and/or range names with invalid names and/or definitions. I would really like to delete them all by code because there are many of them.

by hand is no problem, open the dialog and ONE BY ONE i can delete them .
however if I instruct a macro to do the same error comes saying "error 1004, that name is invalid"
I tried to make them visible, change the definition, but nothing works.
and putting "ON ERROR REUME NEXT" leaves then as they are...

I even thought of manipulating the names dialog by code, but aparently that is impossible...

should I do it by sendkeys or something like that??

I attach the file for your amusement --- whoever makes a code to delete all range names will win my eternal gratitude.
I had to compress the file because otherwise it is more than 1MB ! (no content besides range names...!)



kind regards


Bernat

hi, reading my own words I gave it a shot with sendkeys -- and this is what I came up with
it works, somehow, but while running excel is not very responding and I don't really know what is going on (which I don't like)

if anyone has a better idea (or better implementation with SENDKEYS please let me know!

Sub deleteName()
    Dim i As Integer
     
     For i = 1 To ThisWorkbook.Names.Count
        
        Application.StatusBar = ThisWorkbook.Names.Count - i
     
     With ThisWorkbook.Application
            
            If ThisWorkbook.Names.Count = 0 Then Exit For
            
            
            .SendKeys "%I", 1
            .SendKeys "N", 1
            .SendKeys "D", 1
            .SendKeys "{TAB}", 1
            .SendKeys "{RIGHT}", 1
            .SendKeys "{TAB}", 1
            .SendKeys "{TAB}", 1
            .SendKeys "{TAB}", 1
            .SendKeys "{TAB}", 1
            .SendKeys "{TAB}", 1
            .SendKeys "{RETURN}", 1
            .SendKeys "{ESC}", 1
            

     End With
    Next
End Sub

Give this a try

Sub DeleteBadNames()
Dim nm As Name
Dim vTest As Variant
For Each nm In ActiveWorkbook.Names
    vTest = Empty
    On Error Resume Next
    vTest = Application.Evaluate(nm.RefersTo)
    On Error GoTo 0
    If TypeName(vTest) = "Error" Then nm.Delete
Next nm
End Sub

Thank you Thalassa,

but the problem here is not to check which range names are valid or not, but the fact that invalid names cannot be eliminated the way you suggest.
rm.delete results in the error I posted on top of this thread.

Hi snb,

the reason why I bother is that the problem keeps coming once in a while, there are many sheets in each file, and manipulation with macros even to copy contents out result in excel crash... I think I need to solve the problem somehow

I see that avoids the copy-paste part --- but then I'm affraid images will not get copied, am I right?

A friend told me of this script
http://www.jkp-ads.com/downloadscrip...ameManager.zip

seems to do the trick, even though I'm still fighting with the issue

thank you again snb,

but the problem is not in the formulas -- it's in the nameranges that get automatically copied when a sheet is copied
all old macros and so on get copied even when they are not used in the sheet, it's very weird, and it's actually the origin of all this mess....