VBA – Too many different cell formats

Sometimes with Excel you receive an error message, saying “Too Many Different Cell Formats”. This is really hard to believe, because you have not done something bad or strange and yet you cannot finish your action.

Still, Microsoft knows about this bug (or feature) and has a solution for it. Or 4 solutions for it, published here – https://support.microsoft.com/en-us/kb/213904

However, I will add a fifth solution, which probably (at least for me) works better than the provided four. It is a VBA code, which runs through the file and deletes the styles, which are additional. No Addin-s, simply nothing. Save the file as a XLSM or XLSB and run the VBA.

Then you can work and simply thank me for this 🙂

Here comes the code:

Sub RemoveTheStyles()

    Dim style               As style
    Dim l_counter           As Long
    Dim l_total_number      As Long

    On Error Resume Next

    l_total_number = ActiveWorkbook.Styles.Count
    Application.ScreenUpdating = False

    For l_counter = l_total_number To 1 Step -1
    
        Set style = ActiveWorkbook.Styles(l_counter)
        
        If (l_counter Mod 500 = 0) Then
            DoEvents
            Application.StatusBar = "Deleting " & l_total_number - l_counter + 1 & " of " & l_total_number & " " & style.Name
        End If
        
        If Not style.BuiltIn Then style.Delete

    Next l_counter

    Application.ScreenUpdating = True
    Application.StatusBar = False
    Debug.Print "READY!"
    
    On Error GoTo 0
End Sub

That’s it. It simply works! 🙂