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:

Sub DeleteCustomStyles(Optional ByVal WKB As Workbook)
'----------------------------------------------------------------
'/// Created on:    4-Jan-2013
'/// Created by:    Zack Barresse
'/// 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

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

Sub CallDeleteStyles()
    Call DeleteCustomStyles(Workbooks("Book1.xlsx"))
End Sub

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:

Sub CallDeleteStylesActiveBook()
    Call DELETESTYLES
End Sub

Please note that if you have a lot of styles, this may take a while (several minutes). To give you an example, for a workbook which has 49,000+ custom styles, it take 6 minutes to run. It is well worth it once completed though – clean file which actually opened quickly, from 20 seconds to <1.

Download

Here you will can download an Excel add-in prepared to be enabled directly in Excel and called through a button. To download you will have to be registered and logged in HeelpBook.
[wpfilebase tag="file" id="236"]