this has to be the stupidiest error ever..."too many formats!" but CAN'T DELETE ANY FORMATS...

[sadams1]

hello All,
As you may be able to sense, the level of frustration with this is off the charts...I've read through the posts here & elsewhere online but have not found a way around this one. From what I've found, there's a "4,000 limit" to the number of formats...OK...I've been diligent about streamlining as much of the formatting in a large workbook as possible, there are no "conditional formats", I've deleted multiple sheets thinking this would take down the number of formats but nope!
What on God's green earth is this???

 

[Micron]

You should include your version info in your member details or your posts. I believe it's 4K for xls and 64K for xlsx (perhaps xlsm as well). Maybe try code to see how many you currently have. If it's under the limit that applies to you, I can only think of two possible reasons: the wb is corrupted or b) removing a format may not reduce the count of the formats collection. You could edit the code in that link to skip things you don't want counted as well as have it loop over all your sheets and add up the formats as it runs.

 

[sadams1]

hello Micron & thank you for your time. Sorry about not including important info like the following:
Version 2501 Build 16.0.18429.20132) 64-bit

the code I ran is as follows:

 

[sadams1]

*hit post by mistake! *

Public Sub show_details()


Dim ws As Worksheet
Dim Addresses As String
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
Debug.Print "worksheet: " & ws.Name
Debug.Print "Pivots: " & ws.PivotTables.Count
Debug.Print "Drawingobjects: " & ws.DrawingObjects.Count
Debug.Print "Table objects: " & ws.ListObjects.Count


If ws.Cells.FormatConditions.Count = 0 Then
Debug.Print "Conditional Formatted ranges:0"
Else:
Addresses = ""
Addresses = Addresses & " " & ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).Address
Debug.Print "Conditional Formatted ranges: " & ws.Cells.FormatConditions.Count & " @ " & Addresses;

End If
Debug.Print




Next
On Error GoTo 0
End Sub


***************************************************************************************************

I'm just a hack so I'm looking for the results & am not able to find where they're being logged?

 

[Micron]

Please post code within code tags (use vba button on posting toolbar) to maintain indentation and readability.
We should not assume that just because we know your version number doesn't mean we know for certain if your wb is xls or xlsx (m). So that's my mistake.
In the meantime I was playing around and came up with this:

Sub FormatCount()
Dim sht As Worksheet
Dim i As Long, n As Long

On Error GoTo errHandler
For Each sht In ThisWorkbook.Worksheets
    If sht.Cells.SpecialCells(xlCellTypeAllFormatConditions).Count > 0 Then
        n = sht.Cells.SpecialCells(xlCellTypeAllFormatConditions).Count
        i = i + n
    End If
NoCellsFound:
    If Err.Description = "No cells found." Then Err.Clear
Next
Debug.Print i

exitHere:
Exit Sub

errHandler:
If Err.Description = "No cells were found." Then
    Resume NoCellsFound
Else
    MsgBox "Error " & Err.Number & ": " & Err.Description
End If
   
End Sub

The result is printed in the immediate window (of code editor) if you want a message box instead, change Debug.Print i
to
Msgbox "There are " & i " formats."
This is new to me and I have a feeling it is reporting the number of cells with non-default formats. Each cell could have many formats applied, and I think each one of them counts towards the total allowed. I'll have to keep looking - would be better if an expert could weigh in on this.

 

[MARK858]

AFAIK there is no way of getting the number of format counts Microsoft uses in it's count due to how many variables it uses to define a different format, also Microsoft has never given access to the total that it came up with.

The best that I can suggest is running a program like in the link below to try and clear out corrupt styles.

As with any time you try running code/programs like this that make major alterations I would recommend running it on a copy of the file

XLStylesTool - Free download and install on Windows | Microsoft Store

"Must have" app for Microsoft Excel users. XLStyles Tool is an Open XML based application, which can analyze and clean out corrupted cell styles in your Excel workbook. XLStyles Tool will also perform analysis of named ranges and report potential issues. User of the tool is able to take actions...

apps.microsoft.com

 

[Micron]

AFAIK there is no way of getting the number of format counts Microsoft uses in it's count due to how many variables it uses

I'm beginning to think it would be practically impossible due to the number of formats that can be applied. Since 'format' applies to SO many objects, you might be able to get to that number if you knew every possible object and were willing to loop over every one of them. You would start that code on Friday afternoon and maybe it would be done by Monday morning. The best approach may be to fix/eliminate applied formats and styles using one of the tools available, or copy/paste all sheets into a new wb but don't paste formats.

 

[Alex Blakenburg]

hello Micron & thank you for your time. Sorry about not including important info like the following:
Version 2501 Build 16.0.18429.20132) 64-bit

Please update your account details and specify that you have MS365. You can follow this link Account details or click your user name at the top right of the forum. (Don’t forget to scroll down & ‘Save’)

On a copy of your workbook, it might be worth trying Review > Check Performance, and seeing if that cleans it up.
If it doesn't if you click Home > Cell Styles, what do you see ? Do you see 100s of Custom styles ?

PS: My understanding it that since Excel 2007 the limit is 64,000

 

[sadams1]

Please update your account details and specify that you have MS365. You can follow this link Account details or click your user name at the top right of the forum. (Don’t forget to scroll down & ‘Save’)

On a copy of your workbook, it might be worth trying Review > Check Performance, and seeing if that cleans it up.
If it doesn't if you click Home > Cell Styles, what do you see ? Do you see 100s of Custom styles ?

PS: My understanding it that since Excel 2007 the limit is 64,000

hello Alex
Appreciate the feedback! Updated profile & ran performance & "custom styles". There are LOTS of styles.

 

[sadams1]

thank you all for the insights...this seems impossible so will work around it but greatly appreciate your time!