too many formats issue

[Chrissirhc]

Hi,

If I have a sheet with too many formats I can't add some more. Is there anyway around this? I.e. automatically remove any unused formats?

Thanks,

Chris

 

[AnotherHiggins]

I assume your actual error message is, "Too many different cell formats", right? For future reference, it is always a good idea to include the exact error message.

See this article for info on your error:

http://support.microsoft.com/?kbid=213904

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[vladk]

Range("A1:F10").SpecialCells(xlCellTypeBlanks).ClearFormats

 

[Chrissirhc]

Sorry for my not concise description.

Vladk could you explain what your line of code does exactly?

thanks,

Chris

 

[vladk]

Chrissirhc,

It in the specified range of ("A1:F10") in active sheet, clears all format in blank cells, the cells with no data inside.

vladk

 

[Chrissirhc]

Any other ideas for using unused formats and styles via a macro?

Thanks,

Chris

 

[vladk]

Chrissirhc,

How would you define unused formats and styles?

vladk

 

[AnotherHiggins]

->Any other ideas
What have you tried so far? Did you follow the link in my post to see what Microsoft suggests?


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Chrissirhc]

I looked at the microsoft suggestions. Perhaps I could create a quick macro to go through every cell in the workbook and change the font to Arial, but not change any other aspect of the font...

The empty cell format clearing may not work properly as one day something might not be there and the next it could. On the other hand does it look for empty cell or empty value in a cell?

Thanks,

Chris.

 

[Chrissirhc]

Also, does anyone know a quick way to count number of formats?

 

[Chrissirhc]

Well I cleared some formats and then started trying to standardise the font however a few sheets in I got too many cell formats... which is ironic.

 

[vladk]

Chrissirhc
Why the empty cell format clearing may not work properly as one day something might not be there and the next it could?

It will. It will clear formats in all blank cells of the range. Besides all, I merely gave an idea. I did not set a goal to create dynamic engine to intelligently predict different scenarious and to recreate formats and styles on fly. It goes well beyond this topic.

Again, how would you define unused formats and styles as the actual implementation rests on the answer on this question...

vladk

 

[Chrissirhc]

Too many background colours etc exist that our users use are removed when I ran the code against every cell.

I think you're right I need to define unused formats to get a definitive answer. My post really was just shooting in the dark "what do you guys do to remove formats" hoping that something would pop up... So you're post was in a way exactly what I was looking for... I'll think of common elements which I never want to change that I can replace via a macro such as font...

Do you know how to count the number of formats?

 

[AnotherHiggins]

->Do you know how to count the number of formats?
No.

But here's the thing. According to that MS entry, you only run into a problem when you have around 4,000 different formats. Do you think you might have that many? If so, let me just point out that that is ridiculous. How could you possibly need 4,000 different formats?

It seems very possible to me that the workbook is just corrupted, and that there are not actually too many formats.

But if 4,000 different formats seems possible....

As far as vladk's suggestion goes, you can do it for all cells at once.

And "Perhaps I could create a quick macro to go through every cell in the workbook and change the font to Arial, but not change any other aspect of the font" is WAY over-engineering it.

To try the two things that have been suggested so far (clearing formats from blank cells and changing all fonts to Arial), there is really no reason to use a macro. Just do the following:
[ul][li]Select all cells[/li]
[ul][li]This can be done two ways:[/li]
[ul][li]Click on the square to the left of A and above 1[/li]
[li]Press [Ctrl]+[A][/li][/ul][/ul]
[li]Change the font to Arial[/li]
[ul][li]That's it. The other aspects of the fonts won't be changed[/li][/ul]
[/ul]
With all cells still selected:
[ul]
[li]Go to Edit > Go To > Special > Blanks > OK[/li]
[ul][li]This will only select completely blank cells, not cells that have a formula displaying "" (a zero-length string)[/li][/ul]
[li]Go to Edit > Clear > Formats[/li]
[/ul]

Please post back with the results. I'm curious to know if that helps.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[vladk]

Chrissirhc,

I don't know how to count the number of formats either since none of these objects ThisWorkbook,ActiveWorkbook,
Workbooks expose this property nor implicitely (through the method) nor explicitely.

vladk

 

[Chrissirhc]

Hi Higgins,

The workbook has ~ 50 WorkSheets hence my macro for each worksheet cells.font... etc (which crashed anyway owing to too many formats so I guess Excel does something strange when running the macro). So, I did the manual method and increased number of formats by about 16.

I'll let you know what happens with the other clear formats idea. (although I won't be able to use it in practice... .

Cheers,

Chris

 

[Chrissirhc]

How would I test for a corrupt sheet?

 

[Chrissirhc]

Ok I tried the Range("A1:F10").SpecialCells(xlCellTypeBlanks).ClearFormats for academic reasons and it removed 400 formats. However, it made the sheet look very ugly and is less user friendly.