Fixing too many different cell formats

In a recent webinar I was asked about the “Too many different cell formats” error. This tends to be an error in Excel 2010 and earlier versions. In many cases this error is caused by having too many custom Styles.

Styles are on the Home ribbon and there are many built-in ones, but you can add your own custom Styles.

Extra unwanted custom Styles can be created when copying sheets between files.

Manually deleting the unwanted Styles is time consuming and that is where the macros below are handy.

You can download a file with the macros at the bottom of the post.

If you have Excel 2013 or later check out this post on the Inquire Add-in which can also fix the issue.

If none of these work then see bottom of post for another suggestion.

Two VBA solutions

WARNING: I recommend saving the file with another name eg add v2 to it before using the macros below. Macros can’t be undone and the formats may be altered.

Both these solutions will display a dialog before running the macro. You must click Yes to run the macro.

Both macros work on the active workbook – the file that is active when the macro is run.

If you are unsure how to use macros then read this post.

1. Remove all Custom Styles

In some cases people just want to remove ALL the custom Styles and the macro below will do that.

Be warned, if you have used any of the custom Styles then formats will be affected as the default format is applied when a custom Style is deleted.

If you want to keep the used Styles then use the second solution below.

Sub RemoveAllCustomStyles()
' Removes all custom styles from the active workbook
' User must confirm removal
' I recommend saving the file before running the macro
'
' WARNING: Macro may take many minutes to run if there are
' a large number of custom styles or the file is large

Dim tmpSt As Style
Dim Wkb As Workbook

On Error GoTo HandleExit

If ActiveWorkbook Is Nothing Then Exit Sub

Set Wkb = ActiveWorkbook

If MsgBox("Save the file with a new name eg add v2 to name before trying this macro." _ 
  & vbLf & vbLf & "Continue to remove all custom styles?", 
  _ vbYesNo + vbDefaultButton2, _ "Confirm Removal of all Custom Styles") = vbYes Then

  For Each tmpSt In Wkb.Styles
    With tmpSt
      If .BuiltIn = False Then
        .Locked = False
        .Delete
      End If
    End With
  Next tmpSt

End If

HandleExit:
Set tmpSt = Nothing
Set Wkb = Nothing

End Sub

 

2. Remove unused Custom Styles

If you want to only delete the unused Styles then use the macro below.

This macro shouldn’t affect any existing formats but the warning still applies.

Sub RemoveUnusedCustomStyles()
' Removes all unused custom styles from the active workbook
' User must confirm removal
' I recommend saving the file before running the macro
'
' WARNING: Macro may take many minutes to run if there are
' a large number of custom styles or it is a large file

Dim c
Dim tmpSt As Style
Dim Wkb As Workbook
Dim colStyles As New Collection
Dim ws As Worksheet

On Error GoTo HandleExit

If ActiveWorkbook Is Nothing Then Exit Sub

Set Wkb = ActiveWorkbook

If MsgBox("Save the file with a new name eg add v2 to name before trying this macro." _
  & vbLf & vbLf & "Continue to remove all unused custom styles?", _
    vbYesNo + vbDefaultButton2, _
      "Confirm Removal of all unused Custom Styles") = vbYes Then

'create a collection of used style names - name is also the key
For Each ws In Worksheets
  On Error Resume Next 'creating an existing name will cause an error
  For Each c In ws.UsedRange
    colStyles.Add Item:=c.Style, Key:=CStr(c.Style)
  Next c
  On Error GoTo 0
Next ws

'go thru all the styles and delete those not in the collection
For Each tmpSt In Wkb.Styles
  With tmpSt
    If .BuiltIn = False Then

      On Error Resume Next
      'if style name doesn't exist it will generate an error and
      ' go to the next line of code and unlock and delete it
      If colStyles.Item(CStr(.Name)) <> .Name Then
        .Locked = False
        .Delete
      End If
     On Error GoTo 0
    End If

  End With

Next tmpSt

End If

HandleExit:
Set tmpSt = Nothing
Set Wkb = Nothing
Set ws = Nothing
Set colStyles = Nothing

End Sub

 

[button href=”https://a4accounting.com.au/wp-content/uploads/2018/06/Remove_Styles.xlsb” primary=”true” centered=”true” newwindow=”true”]Download Example File[/button]

Alternate solution

Check out this site for a non-VBA solution – it is more complex but it may work when other solutions fail.