too many different cell formats

A user is getting "too many different cell formats" error. It is a very large model.

1 - Besides cleaning up the excel spreadsheet is there a way to work around this?

2 - Can I tally the amount of format's in the spreadsheet. I know they hit the hard 4000 limit, but would like to make certain changes to see the affect it has on the total.

 - Also they mentioned that because of the Style of the worksheet when they do try to change certain formats it reverts after they save and close - can anyone elaborate on this for me.

Thanks - SJMP
sparkisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
I believe you can use VBA and Styles.Count, e.g.

Sub howmanystyles
MsgBox(ActiveWorkbook.Styles.Count)
End Sub
0
sparkisAuthor Commented:
Thanks - but I dont know squat about VBA how can I do this?
0
cyberkiwiCommented:
ok, hit Alt-F11
In the tree on the left, double click on Sheet1 - the right becomes a white big text area
Paste the code from Sub x. .. through End Sub
Highlight any row inside the code
Press F5

When you are done, just select all text and delete it and close the "Microsoft Visual Basic" window
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

sparkisAuthor Commented:
I pasted the code
Sub howmanystyles
MsgBox(ActiveWorkbook.Styles.Count)
End Sub

and closed VBA and highlighted the entire spreadsheet hit F5 opens a box that says print area
0
Saqib Husain, SyedEngineerCommented:
Instead of F5 tru alt-f8 and then press run
0
sparkisAuthor Commented:
that did it thanks - now I have about 50 spreadsheet in this worksheet. Can I ask this to run on the whole worksheet?
0
cyberkiwiCommented:
Hi there,

instead of >> and closed VBA and highlighted the entire spreadsheet hit F5 opens a box that says print area

Leave VBA running, click on any row in the VBA code, then hit F5 while in the VBA window.

Cheers
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cyberkiwiCommented:
Hi,

It is counting the Workbook, so it is across all sheets.
0
sparkisAuthor Commented:
But it tells me 294 - and it hit the 4000 mark so that cannot be right.

I just went back into VBA and pasted the code into the box for Sheet 34. When to Sheet 34 and selected it to run the code for sheet 34. gave me the same number.
0
cyberkiwiCommented:
If you are amenable to forking out money, this will help you
http://www.rowingservice.com/quarrell/QAid/

I think I saw "Styles" in the question and jumped.
Re the 4000 limit
>> It includes ALL formatting and any cell with a unique combination adds to the total number of different cell formats.
0
sparkisAuthor Commented:
so I must be doing something wrong. It has hit the 4000 limit. But the VBA script tells me 294
0
cyberkiwiCommented:
The script I provided only counts the different Style objects.
There are also borders, alignment, etc objects to count and frankly too many combinations between the formatting objects.
0
Saurabh Singh TeotiaCommented:
Have a look over this...
http://www.experts-exchange.com/Q_21046037.html
Saurabh...
0
DaveCommented:
From personal experience - which supercedes my post in http://www.experts-exchange.com/Q_21046037.html above -  the  best tool that I have found for removing this problem from large  spreadsheets (where manually removing the formatting  in large chunks is not practical or desireable) is by a crowd called  xlsgen, http://xlsgenreduction.arstdesign.com/index_en.html.  While it does cost $50 it is a must have utility

Free  tools - that from my experience don't achive the same quantity of  reduction as xlsgen - but will still help include Leo Heuser's code at http://www.j-walk.com/ss/excel/eee/eee007.txt  and ASAP utilities, http://www.asap-utilities.com/

Cheers

Dave
0
sparkisAuthor Commented:
didnt really fix anthing but helpful
0
DaveCommented:
Hi,

the xlsgen tool will fix the problem

Regards

Dave
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.