Fixing too many different cell formats

A VBA solution or two

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

Download Example File

Alternate solution

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

 

Related Posts

Posted on
Categories: Excel 2010, Excel Blog, Macros

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

7 thoughts on “Fixing too many different cell formats”

  1. Thank you very much for this – the macro helped me remove the thousands of accumulated styles in our office workbook which eventually made it impossible to copy and paste from one workbook to another. Much appreciated!

    Reply

  2. Brilliant – worked perfectly. Couldn’t even resize the font or bold before using this now completely free to change the format.

    Reply

  3. Dear Neale, great codes. Is it possible to have excel application open and yet activeworkbook is nothing? My view is that if excel is open, then a workbook has to be active. Please confirm my understanding.

    Reply