PDA

View Full Version : Solved: Is Insert > Name > Create evil?


TrippyTom
07-29-2008, 01:12 PM
This is not a programming question, but one that I'm curious about. Yesterday, I encountered a client that was trying to copy a tab from one worksheet to another, but it wouldn't let him because it said the sheet had "too many formats".

I tried Edit > Clear > All on every cell outside his printable range, but that didn't help. Then someone else pointed out to me that the file had literally THOUSANDS of named ranges! And they said Excel has a "feature" that creates these automatically if you choose one or more of the options in Insert > Name > Create (see pic).

What's the point of this "feature" if it's going to corrupt a file?
Bob Phillips
07-29-2008, 01:24 PM
It is like everything, if abused it creates problems.

\it is a very useful feature for quickly adding a set of range names.
TrippyTom
07-29-2008, 02:37 PM
Ok, but how did the user end up with thousands of named ranges? Is this window the cause of the problem?

They couldn't have done that intentionally, could they? Some of the names had strange characters in them, so obviously it wasn't something they named intentionally.
Bob Phillips
07-29-2008, 02:50 PM
I can't say, I wasn't there when they did it. But if they were in a big dataset and they ticked some boxes, it is possible to creat many names.
TrippyTom
07-29-2008, 02:55 PM
Ok, thanks XLD for the clarification. I will just warn people to use it very carefully, or not at all. We don't want this to happen again, and since I'm not sure what caused it, I will just tell people to use caution as this may be one of the sources of the problem.

(marking the thread solved)
Aussiebear
07-30-2008, 01:17 AM
Would Microsoft provide a solution here?
TrippyTom
08-12-2008, 01:00 PM
This problem will not die. Now the person who originally had this problem wants to remove them. I'm thinking maybe I can write a macro to remove ALL named ranges, but wouldn't that be the same as starting from scratch in a new file?
Bob Phillips
08-12-2008, 02:27 PM
Get hold of Jan Karel Pieterse's NameManager utility, http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp, that will allow you to pick out blocks and delete them.