«Back

Too Many Cell Formats in Excel (Resolved)
Posted by TJ on Friday March 26, 2010 @ 12:38 PM
[Tags: guide, excel, accounting]



If you are trying to convert a Microsoft Excel 2007 file to open with Excel 2003 you may receive an error that there are "Too Many Cell Formats". After spending quite sometime I found a fix for this. You need to open the file in the Excel 2007 and run the macro below and then resave the file. Before you do anything make sure you save your file in case you don't like the results.

OPTION 1: The macro below will show a prompt "Delete Style ____" for each style which and you will need to click "YES" for each style. If you are not concerned about deleting a valid style you can use OPTION 2

Sub DeleteStyles()
'
' DeleteStyles Macro
'
Dim styT As Style
Dim intRet As Integer

For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then
intRet = MsgBox("Delete style '" & styT.Name & "'?", vbYesNo)
If intRet = vbYes Then styT.Delete
End If
Next styT
End Sub


OPTION 2:The macro below will delete all unused styles without a prompt. This is faster but can be more dangerous than OPTION 1. Make sure you save the file both before and after running the macro (thanks to Nick)


Sub DeleteStyles()
'
' DeleteStyles Macro
'
Dim styT As Style
Dim intRet As Integer

For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then
styT.Delete
End If
Next styT
End Sub




OPTION 3: For those that do not want to deal with Macro's there is a tool you can download that I also find very helpful and easy to use. The tool removes unused cell styles, "stubborn styles" that you can't delete through Excel UI, bad named ranges, named ranges with external references, and unhides non-system created hidden named ranges. Download XLCleaner here: https://skydrive.live.com/?cid=53e1d37f76f69444&id=53E1D37F76F69444!526&sc=documents



Reader Comments (Page 2 of 2) 1 | 2 |
Post a Comment:
Comment:
Name:
Security Code Security Code:
Comments with offensive language will be automatically deleted within 24 hours.