18

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

0

6 Answers 6

24

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.

2
  • Useful one,,, ☺ Mar 18, 2019 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, 2020 at 2:02
9

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
2
  • 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, 2019 at 12:49
  • Using On Error Resume Next and On Error GoTo 0 is IMHO bad coding style. You can safely remove those, I guess.
    – Vince42
    Aug 20, 2022 at 8:10
3

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

1
  • welcome to super user and thanks for your answer, always good for a non vba alternative !
    – PeterH
    Jan 21, 2021 at 11:17
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
10
  • Actually I like the way you loop over Styles rather than my looping over an index. Feb 1, 2018 at 17:07
  • Will have problems here, deleting a collection forwards.
    – AJD
    Jul 6, 2018 at 8:03
  • @AJD Hi, thanks for your comment, what problems could be caused from this ?
    – PeterH
    Jul 6, 2018 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, 2018 at 21:25
  • 1
    Microsoft has this exact same code, albeit with comments and more directions support.microsoft.com/en-us/topic/… Sep 14, 2022 at 17:14
2

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.

1
  • Definitely, the way to go.
    – Guile
    Oct 25, 2022 at 13:10
2

Very simple way. step-1 Rename excel file as .zip step-2. Extract file in same folder "Extract Files Here" Step-3. Open Styles.xml with note pad. Step-5 Select from to and delete. Step-6 Save file, exit from subdirectory and convert all files to ZIP Step-7 Rename .zip as .xlsx Step-8 Open with excel and enjoy

1
  • thanks for your answer ! i wouldn't say this is a simple way tho :)
    – PeterH
    Jun 22, 2022 at 8:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .