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

[bagullo]

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

[bagullo]

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!

Please Login or Register  to view this content.

[snb]

I wouldn't bother.
Copy the contents of the worksheets to a new workbook and save that as the file you want to work with.

[Thalassa]

Give this a try

Please Login or Register  to view this content.

[bagullo]

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.

[bagullo]

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

[snb]

result in excel crash...

That may be due to the same problem; try

Please Login or Register  to view this content.

[bagullo]

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

[bagullo]

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

[snb]

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

Right, but in that case you could also try:

Please Login or Register  to view this content.

[bagullo]

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....

[snb]

I know, but when these named ranges are being used in a formula it messes up the copying process, hence the crashing of Excel.