15
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.
Useful one,,, ☺ – Rajesh S – 2019-03-18T09:06:38.470
3
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
thanks PeterH for editing :-) – Andreas – 2019-05-09T12:41:30.400
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 – 2019-05-09T12:49:37.660
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
Actually I like the way you loop over Styles
rather than my looping over an index. – Gary's Student – 2018-02-01T17:07:04.023
Will have problems here, deleting a collection forwards. – AJD – 2018-07-06T08:03:55.003
@AJD Hi, thanks for your comment, what problems could be caused from this ? – PeterH – 2018-07-06T09:16:49.500
@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: https://stackoverflow.com/questions/18858718/word-vba-shaperange-delete-unexpected-behavior/18859393#18859393 , https://stackoverflow.com/questions/45585393/vba-loop-and-delete-issue
@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 – 2018-07-09T07:09:20.147
@PeterH: Then that is by luck, rather than by design. – AJD – 2018-07-09T19:16:50.433
@AJD please do explain, I am not good with VBA so if I can learn something that would be great. I know you have said about j is = 1
but I do not use that anywhere in the macro – PeterH – 2018-07-10T07:18:26.067
@PeterH, I have used j
because the autocorrect in this box keeps capitalising I
on me! Have a read of those links I put in my comment, they have answers that discuss why. – AJD – 2018-07-10T07:20:02.640
Very easy with VBA – Gary's Student – 2018-02-01T16:53:25.340