Link to home
Start Free TrialLog in
Avatar of nitsali
nitsali

asked on

"Too many format cells" error masage in excel

While working on an excel file I've got the following error masage : "Too many format cells". I don't mind see this masage but after saving and trying to reopen I couldn't open the file by given this masage again. After a deep investigaion I realized that the Excel keep in Format -> Style a lot of format styles while working on a file. To be able to open the file all styles should be deleted from this location. If you open a very large file with a lot of formating cells you will find hundreds of styles, beside 7 of them the rest are not necessery. The delete botten allow you to delete only one style each time.
Do you know how can I delete all non necessery styles efficiency?

Thanks
nitsali
ASKER CERTIFIED SOLUTION
Avatar of ahammar
ahammar
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vbkid
vbkid

Hi nitsali & ahammar,
I too had the same problem.What happened was when I tried to format a cell Excel gave an error saying that 'Too many cell formats'. Then when I closed & opened the file again & deleted few rows that needs formatting, and reformatted them the way I want & it worked. I never suspected that is a virus. ahammar, thanks for the info. Now I have another error, may be similiar, I had put a list of values in one sheet and had a drop down list referring to this in another sheet. The application broke, saying, 'Not enough available resources'. I am working on it.


vbkid.
nitsali:
I just thought of something. Even if it isn't the virus, (but I'm almost positive it is), the macro I was referring to will still delete all the styles except for the default ones. Let me know if you want it.

vbkid,
I haven't seen the virus cause the other error you mentioned, but it's possible. The virus isn't the only thing that causes the 'Too many cell formats' error, but the way nitsali described it, I'm pretty sure it is. That doesn't mean that you had/have the virus, but there is a chance of it.

You better join in on my New Years game....:-)


Cheers!
ahammar
Avatar of nitsali

ASKER

ahammar
First of all thank you very much for the long and detailed answer.
Like vbkid I didn't even though about a virus as the cause. But I will check it out.
About the Macro which deletes the styles, I've already done it before, thanks. I just though there is a way to get into some definition which reset this bank of styles.
For the Virus information, Its greate, I will check and use it.

By the way, Is your macro jump on the styles which are grayed and couldn't be deleted?

Thanks again, good luck and Happy new year
nitsali
Hi nitsali,
You are welcome. If I understand your question, then yes. The macro also gets rid of the ones that gray out the delete button.
There is no way to reset the styles. I know that for sure because Microsoft told me that directly.
Actually, there is no way to really delete the grayed out ones, not even with a macro. The way my macro does it is by openning a new workbook, and moveing everything to it except for the things you don't want. It was a difficult thing to do and took me a long time (although I could probably do it a lot faster now because I know a lot more than I did then) but it works really nice. I sent it to 2 people so far and they were both happy with it. One of them even sent me a gift certificate for 100.00 because they used it to clean up over 400 files.
Anyway, enough bragging....
If I missunderstood your question, let me know.
You are welcome to have a copy of the macro if you want it.

Cheers!
ahammar
Avatar of nitsali

ASKER

You did a great job, it will be nice to have your macro.
nitsali@hotmail.com
thanks again
nitsali