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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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 |