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
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityIs 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
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.
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
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
Styles
rather than my looping over an index.
– Gary's Student
Feb 1 '18 at 17:07
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
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).
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.