Delete ALL custom cell styles EXCEL

Question

Is it possible to delete ALL the custom/created cell styles in a workbook ? Just leaving the default styles.

Without having to delete them all one by one

Answer 1

Try this small VBA macro:

Sub StyleKiller()
    Dim N As Long, i As Long

    With ActiveWorkbook
        N = .Styles.Count
        For i = N To 1 Step -1
            If Not .Styles(i).BuiltIn Then .Styles(i).Delete
        Next i
    End With
End Sub

This resolves the Builtin vs Custom issue. Note we run the loop backwards to avoid corrupting the loop index.

Answer 2

To simply remove all without using INDEX, try the below:

Sub StyleKiller()
    Dim st As Style
    On Error Resume Next
    For Each st In ActiveWorkbook.Styles
      If Not st.BuiltIn Then
        st.Delete
      End If
    Next
    On Error GoTo 0
End Sub

Answer 3

Ok, this wasn't as hard to do as I first thought.

Bit messy as I don't often use vba; but this code will roll back to just the default styles:

Sub DefaultStyles()
   Dim MyBook As Workbook
   Dim tempBook As Workbook
   Dim CurStyle As Style
   Set MyBook = ActiveWorkbook
   On Error Resume Next
   For Each CurStyle In MyBook.Styles
      Select Case CurStyle.Name
         Case "20% - Accent1", "20% - Accent2", _
               "20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _
               "40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _
               "40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _
               "60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _
               "Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _
               "Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _
               "Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _
               "Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _
               "Note", "Output", "Percent", "Title", "Total", "Warning Text"
         Case Else
            CurStyle.Delete
      End Select
   Next CurStyle
   Set tempBook = Workbooks.Add
   Application.DisplayAlerts = False
   MyBook.Styles.Merge Workbook:=tempBook
   Application.DisplayAlerts = True
   tempBook.Close
End Sub

Answer 4

All of the above works but our work pc's are quite dated so the macro's kept crashing. For some reason a lot of the files have hundreds of cell styles which slows down the docs.

My solution turned out to be easy and quick.

Select all the worksheets in your workbook and copy them to a new book. This maintains all the external links and formats and everything but the styles does not copy across.

Save your new workbook over the old one (Close the old one first).

Answer 5

The issue of custom styles is also discussed in http://support.microsoft.com/kb/213904

I found that installing the free "XLStyles Tool" from MS Store as suggested in the KB article was a very convenient way to delete the custom styles.