Too Many Cell Styles

To get that error you'd need to have tens of thousands of unique cell styles. At my company, the worst I've seen is around 30,000 cell styles.

I only see that occurring when the source workbooks have been generated by a reporting system.
It seems that many of them
a) create unique cell styles each time the report is run
and
b) somehow reuse the same Excel workbook template, accumulating large numbers of cell styles.

However it happens, I run this code to clean them out.
Note: At least where I work, I usually end up with a handful of "ghost" cell styles that appear, but cannot be selected.

Please Login or Register  to view this content.

When you're done, delete that module.

OR you can put that code in your PERSONAL.XLSB file so the macro is always available.

Is that something you can work with?

I see the same issue when I am given files from other people and it drives me crazy because I heavily rely on the default styles to keep my models tidy.

I use the same script shown above.

To put the code into a specific workbook:
• ALT+F11…to open the Visual Basic Editor
• Right-click on your workbook's name in the VBA - Projects window
…Select: Insert Module
• Copy the VBA code and paste it into that module:

------------------------------------------------
To make the code available whenever Excel opens, you'd need to save it in a PERSONAL.XLSB
Which Excel will create for you if you do this:
• Display the Developer ribbon tab
...File.Excel_options...Popular tab
...Check: Show Developer Tab
...Click: OK

• Click the Record Macro button (which should display on the Status Bar, next to "Ready")
...Store macro in: Personal Macro Workbook
...Click: OK
(the Record Macro button will become a Stop Recording Macro button)

• Click the Stop Recording Macro button

• ALT+F11…to open the Visual Basic Editor
• Right-click on PERSONAL workbook's name in the VBA - Projects window
…Select: Insert Module
• Copy the VBA code and paste it into that module:

After you close Excel the PERSONAL.XLSB file will be saved in Excel's startup folder and will be available whenever you open Excel
------------------------------------------------

Either way, you'd run that macro by:
• Press F8 (to open the Macros window)
...Select: ResetCellStyles.....Click: Run


Does that help?

Note: If you want to save a workbook that contains macros, you need to
• File.Save As
• change the file type to either .XLSB or .XLSM

Yeah or you can do what I did, which is have a standalone file to open.

Open it.
Switch back to the file you want to clean.
Hit Ctrl+Shift+C and an input box will popup asking if you are sure you want to purge XX cell styles.

CellStyleKiller.xlsm

The code is about the same as above, but I have a little input box that pops up with a total number of cell styles, mostly so I can laugh.

One time I had a file with over 20,000 cell styles. o_O

The code:

Please Login or Register  to view this content.

Originally Posted by Dragg64

Hello, Just to inform that the codes above are not working in Excel 365

This post was from January 2015 and not for Excel 365.