Too Many Cell Formats

The maximum number of cell styles in a workbook (Excel 2007 and above) is 65,000 (or 65,476).
If you workbook contains more than this number you will see one of the following messages.


Known Bug - Unused Styles Are Copied Unexpectedly.

This was a known bug in Excel 2007 (and is still a bug in Excel 365) that is still not fixed.
When you move or copy a worksheet from one workbook to another, unused styles are copied unexpectedly.
There is a hotfix that can be applied to stop these unused styles being coped across.

HKCU\Software\Microsoft\Office\16.0\Excel\Options\  DisableCopyUnusedCustomStyles - DWORD (32 bit) - Value = 1 

Known Bug - Not working when is Excel running in separate instances

The hotfix described above only works when you are copying to and from workbooks in the same Excel instance.
If you have the "DisableMergeInstance" entry in your Excel registry options then the hotfix will not work.

HKCU\Software\Microsoft\Office\16.0\Excel\Options\  DisableMergeInstance - DWORD (32 bit) - Value = 1 

link - social.technet.microsoft.com/Forums/windows/en-US/710c7bae-eb24-4bf5-93dc-ceb453678a76/disablecopyunusedcustomstyles-not-working?forum=excel
link - excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/35345938


Solution

The only way to remove a style using the User Interface is to right mouse click on an individual styles and choose delete.
If you want to remove a lot of styles you will have to use some VBA code.
This subroutine will remove all the custom styles, even ones that are being used.

Public Sub RemoveALLCustomStyles() 
Dim oStyle As Excel.Style
   For Each oStyle In ActiveWorkbook.Styles
      If Not styT.BuiltIn Then
        oStyle.Delete
      End If
   Next oStyle
   Call MsgBox("All Custom Styles have been removed")
End Sub

This subroutine will remove all the custom styles that are not being used.
This first examines the workbook and saved a list of all the used styles into a Dictionary object.
Before running this code you will need to add a VBA reference to the Scripting.Runtime.
Warning: If you have 60,000+ styles in a workbook this code could take 20 minutes to run.

Public Sub RemoveUNUSEDCustomStyles() 
Dim oStyle As Excel.Style
Dim oCellRange As Excel.Range
Dim oWsh As Excel.Worksheet

Dim sStyleName As String
Dim iStyleCount As Long
Dim oScriptingDictionary As New Scripting.Dictionary
Dim aKey As Variant

  For Each oStyle In ActiveWorkbook.Styles
    If (oStyle.BuiltIn = False) Then
      sStyleName = oStyle.NameLocal
      iStyleCount = iStyleCount + 1
      oScriptingDictionary.Add sStyleName, 0
    End If
  Next oStyle

  For Each oWsh In ActiveWorkbook.Worksheets
    If oWsh.Visible Then
      For Each oCellRange In oWsh.UsedRange.Cells
        If Not oCellRange.Style.BuiltIn Then
          sStyleName = oCellRange.Style.Name
          oScriptingDictionary.Item(sStyleName) = oScriptingDictionary.Item(sStyleName) + 1
        End If
      Next oCellRange
    End If
  Next oWsh

  For Each aKey In oScriptingDictionary.Keys
     If oScriptingDictionary.Item(aKey) = 0 Then
        ActiveWorkbook.Styles(aKey).Delete
        If Err.Number <> 0 Then
           Err.Clear
        End If
        oScriptingDictionary.Remove aKey
      End If
   Next aKey
End Sub

Public Sub RemoveUNUSEDCustomStyles2() 
Dim oStyle As Excel.Style
Dim oCellRange As Excel.Range
Dim oWsh As Excel.Worksheet

Dim sStyleName As String
Dim iStyleCount As Long
Dim oCollection As Collection
Dim oObject As Variant
Dim oItemExists As Variant

  Set oCollection = New Collection
  For Each oWsh In ActiveWorkbook.Worksheets
    If oWsh.Visible Then
      For Each oCellRange In oWsh.UsedRange.Cells
        If Not oCellRange.Style.BuiltIn Then
           sStyleName = oCellRange.Style.Name
           oCollection.Add sStyleName
        End If
      Next oCellRange
    End If
  Next oWsh

  For Each oStyle In ActiveWorkbook.Styles
    If Not oStyle.BuiltIn Then
       On Error Resume Next
       oItemExists = oCollection.Item(oStyle.Name)
       If (oItemExists = Empty) Then
         oStyle.Delete
       End If
    End If
  Next oStyle
End Sub

Windows App - xlStylesTool

This can be found in the Windows App Store


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext