MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Too many different cell formats error

  Asked By: Maria    Date: Feb 05    Category: MS Office    Views: 1356

I am copying sheets from different workbooks to a master workbook.

There are ± 100 workbooks that I am copying from.

I am now receiving the " Too many different cell formats" message.

I have tried to read up as much as possible on this and have learned
that there is a limit of 4000 formats - after which problems occur.

Is there a way to determine the number of formats in a workbook /
worksheet so I can determine which supporting workbook(s) are causing
the problem.

I have inserted the following code to try to track this
"MsgBox ActiveWorkbook.Styles.Count" but this is clearly reporting
something else as when the error message is received, only 233 styles
are reported in the master workbook.



1 Answer Found

Answer #1    Answered By: Hollie Hughes     Answered On: Feb 05

This limitation is a pain in the neck. AFAIK there is no way to access a
count of the number of formats.

One issue is that a cell  format is any different attribute of a cell. for
example if you have a cell with a thin bottom border, that is a different
format to a cell with a thick bottom border. Each colour (background and
foreground) can be different, fonts have a number of attributes, etc. So the
number of possible formats  (combinations of all the attributes) is enormous.

If you start with a blank sheet, then

Select some cells and make them bold (you have two formats)
Select some cells including some but not all of the bold ones and make them
red (you have four formats)
Select some of the cells and set a bottom border (you now have up to 16
formats) - remember some cells may have top borders now, some bottom, some
none, and some both and each of these can have bold or non bold fonts and they
may be read or default .

This is made even more difficult because excel is very reluctant to lose
cellformats, even if they are not in use.

If you now select an "unformatted" cell and copy it
Select all cells
Paste Special - formats
All the cells are now unformatted

But: You have still used 16 formats!

To work around this you might think you could simply import the workbooks, one
at a time and then select the newly imported workbook  and delete all the
formatting or remove the formatting from each workbook before importing.

It will not work.

You may need to import the values only from each workbook. For example by
using copy and PasteSpecial values.
You might also PasteSpecial formats if you need the formats. This should only
paste in the formats which are really in use (I think).

I hope some of this may help. Please let us know how you get on.

Didn't find what you were looking for? Find more on Too many different cell formats error Or get search suggestion and latest updates.