Delete Styles in MS Excel

 

This is applicable to Excel 2007-2013.

Styles are wonderful, and I’ve loved having them in Excel.  If you’re not familiar with styles, check out these links:

http://office.microsoft.com/en-us/word-help/style-basics-in-word-HA010230882.aspx (Word 2007+)

http://office.microsoft.com/en-us/word-help/all-about-themes-quick-styles-cell-styles-and-background-styles-HA010178624.aspx (Excel 2007+)

http://office.microsoft.com/en-us/excel-help/using-styles-in-excel-HA001110199.aspx (Excel 2003)

Often times, especially when sharing files, perhaps emailing files back and forth to/from co-workers, a file can become bloated with styles.  If you look on the Home tab, in the Styles group, you’ll see these default styles:


If you have plenty of custom styles, perhaps you’ll see a mess like this:


Seems ridiculous, but it happens.  These can make an otherwise efficient file and bog it down quite a bit.  If you notice a file opening slower than normal, or slower over time, check how many styles you have because they may be bloated.

To start with a clean slate, sometimes it’s better to just delete them all.  This can be accomplished fairly easily with some VBA code.

This routine will delete all custom styles (not built-in) of the specified workbook:

[sourcecode language=”vb”] Sub DELETESTYLES(Optional ByVal WKB As Workbook)
‘—————————————————————-
‘/// Created on: 4-Jan-2013
‘/// Created by: Microsoft MVP
‘/// Purpose: Deletes all custom styles in specified book.
‘—————————————————————-
Dim TempStyle As Style

If WKB Is Nothing Then
If ActiveWorkbook Is Nothing Then Exit Sub
Set WKB = ActiveWorkbook
End If

If MsgBox(“Delete all custom styles?”, vbYesNo + vbDefaultButton2, _
“DELETE CUSTOM STYLES?”) <> vbYes Then Exit Sub

For Each TempStyle In WKB.Styles
If TempStyle.BuiltIn = False Then
TempStyle.Locked = False
TempStyle.Delete
End If
Next TempStyle

End Sub [/sourcecode]

To call this function, you can use a line like this, specifying the workbook:

[sourcecode language=”vb”] Sub CallDeleteStyles()
Call DELETESTYLES(Workbooks(“Book1.xlsx”))
End Sub [/sourcecode]

If you want to call the routine to perform this on the active workbook (whichever workbook has the current focus – beware, it may be problematic), you could use something like this:

[sourcecode language=”vb”] Sub CallDeleteStylesActiveBook()
Call DELETESTYLES
End Sub [/sourcecode]

Please note that if you have a lot of styles, this may take a while.  To give you an example, this post was predicated on me having to do this for a workbook which had 49,000+ custom styles, and it took 6 minutes to run.  It was well worth it once completed though – clean file which actually opened quickly, from 20 seconds to <1.