Invalid Range Names - Imposible to fix?

[ParanoidAndroid]

I've spent a lot of today trying to find a way to fix invalid range names in a Xp/2002 excel workbook.

The problem has been compounded over time by people copying sheets from one workbook to another which includes all the range names (including the invalid ones) to the new workbook.

The file I was given to fix this morning had over 22,000 range names in it - of which a handful were used and less than half were correctly named!

The only way I have found to correct a invalidly named range name is to change the reference style to/from R1C1 and key in a new name. This would be fine for a few range names, but anything more than twenty or so is a pain, and 10,000 is a full time job for life!!

I was able to use a dataloader program to systematically rename the invalid range names in this one workbook, but an ideal solution would not require a third party piece of software. This is how I know roughly how many invalid names there were... the dataloader limit is 10,000 rows and excel was still prompting for new names!

Once I had renamed everything I was able to run a compact piece of VBA to delete all the useless range names. The file size went from 14.3M to 590K and now loads in an instant.

My question is have any of you come across this problem (and solved it!) before? Why is it impossible to rename or delete a range name when it has invalid characters in it? Surely the ONLY thing you should be able to do is rename or delete it!

I can attach sample files if I've not made myself clear, but as I stated earlier the original file is very big!

 

[chergh]

Never had to deal with it but you should be able to rename them by looping through the names collection. So it would be something like:

set nme = thisworkbook.names

for i = 1 to nme.count
nme(i).name = "newname" & i
next i

Now I don't have access to excel at the moment so cant confirm this works, but I would expect it to as it uses ordinal position.

 

[ParanoidAndroid]

You would think that... I know I did.

The problem appears to be that excel refuses to let you modify (or delete) in any way a range name that is not currently a "valid" name!

I have heard a rumour that this is improved, or maybe even fixed in 2007... but I don't see my firm moving to 2007 in the near future... our IT policy is "no cutting edge"