Too Many Different Cell Formats

[Guest]

I have a large spreadsheet with many different formats. I now get an error
message that states "Too Many Different Cell Formats". Is there something
that would allow me to continue with adding more different cell formats?

Also, as I have been trying to get consistency in my document, I keep
getting the error message and am not allowed to bring consistency to the
formatting.

 

[Guest]

Find some info here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;213904

You're likely going to have to clear some formatting and format from scratch.

tj

 

[David McRitchie]

Haven't that one come up very frequently anymore.
also see Harold Staff's post to help you clear out unused formats
with his updated code in
http://groups.google.com/groups?threadm=OxP9cgi1AHA.1572@tkmsftngp02
probably a lot more useful than the MS KB article.

The links are on my if you want the check out the original articles
http://www.mvps.org/dmcritchie/excel/slowresp.htm#formats

 

[Guest]

Thanks for the help. Someone is going to help me install the code today. Do
you know how much space this might free up? I'm thinking this might be a
temporary solution because my spreadsheet will most likely continue to grow.
Would transfering the data into Access be a viable solution?

 

[David McRitchie]

Hi Nanette,
Unless you did something like format individual cells rather than
large groups of cells you are likely to recover much space.

The problem is that each different format that you have has to be
stored as a format number for you. So if you make the top
border red and have $formatting that is one combination,
another with red top border and general formatting is another
combination, etc., etc, etc. -- which is
another reason to format large areas at once, but is not
going to save much in size of a file.

To save space you might also have a macro installed to free up
some unused space that Excel does not keep very good track of.
Would suggest installing the DeleteUnused macro at
http://www.contextures.com/xlfaqApp.html#Unused
because if you think you have a space problem you probably do.
Use Ctrl+End to see where Excel has your last cell at.

Installing macros is not difficult see
http://www.mvps.org/dmcritchie/excel/excel.htm
and for worksheet event macros see
http://www.mvps.org/dmcritchie/excel/event.htm#blueboxWS

I hope they are only going to assist you, rather than install macros for
you everytime you want to install a macro that you already have the
coding for.

 

[David McRitchie]

correction:

 

[Guest]

Hi David,

I took your suggestion and put in the macro myself which I copied from your
post at
http://groups.google.com/groups?threadm=OxP9cgi1AHA.1572@tkmsftngp02.
Unfortunately, it isn't working. I'm getting syntax errors and am not sure
how to correct them.

Any suggestions?

 

[Guest]

David,

Someone came by and helped me to fix the code. It was broken incorrectly on
different lines. It worked!

Thanks so much. Hopefully this will get the spreadsheet running for awhile.
I'm a temporary worker at the moment.