13

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

enter image description here

Improve this question
1
17

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.

Improve this answer
2
  • Useful one,,, ☺ – Rajesh S Mar 18 '19 at 9:06
  • For some reason, when deleting the first non built in style it displayed a generic error message. Andreas' solution below worked though. – Andrew Jun 6 '20 at 2:02
7

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
Improve this answer
1
  • thanks for answering ! its a LOT simpler than my version, I will test later, and if it works I will accept as correct answer – PeterH May 9 '19 at 12:49
2

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
Improve this answer
8
  • Actually I like the way you loop over Styles rather than my looping over an index. – Gary's Student Feb 1 '18 at 17:07
  • Will have problems here, deleting a collection forwards. – AJD Jul 6 '18 at 8:03
  • @AJD Hi, thanks for your comment, what problems could be caused from this ? – PeterH Jul 6 '18 at 9:16
  • @PeterH: deleting forwards means that items in the collection are renumbered. For example, in the loop, j is = 1 and then you Delete(1). (2) moves to (1), but j increments to 2, so the next step is Delete(2), which is the old (3). For Each is an enumerating loop, so the same logic is used. There was a good explanation on StackOverflow somewhere - I can't find it, but these also explain: stackoverflow.com/questions/18858718/… , stackoverflow.com/questions/45585393/vba-loop-and-delete-issue – AJD Jul 6 '18 at 21:25
  • @AJD Thanks for the info, the macro itself works as intended, I have used it on several workbooks since I posted it back in Feb – PeterH Jul 9 '18 at 7:09
1

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).

Improve this answer
1
  • welcome to super user and thanks for your answer, always good for a non vba alternative ! – PeterH Jan 21 at 11:17
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.