Deleting Unwanted Styles

Still works perfectly in Microsoft 365 version in 2023!

I do like the approach in the comment by "millawitch" also. i didn't try it but it makes sense.

Thanks for all you do, Allen!

-Sharon

How to programmatically reset a workbook to default styles

(Source: https://support.microsoft.com/en-gb/topic/how-to-programmatically-reset-a-workbook-to-default-styles-36e94af7-d185-68fb-3962-0882a5260132)

Sub RebuildDefaultStyles()

'The purpose of this macro is to remove all styles in the active
'workbook and rebuild the default styles.
'It rebuilds the default styles by merging them from a new workbook.

'Dimension variables.
Dim MyBook As Workbook
Dim tempBook As Workbook
Dim CurStyle As Style

'Set MyBook to the active workbook.
Set MyBook = ActiveWorkbook
On Error Resume Next
'Delete all the styles in the workbook.
For Each CurStyle In MyBook.Styles
'If CurStyle.Name <> "Normal" Then CurStyle.Delete
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"
'Do nothing, these are the default styles
Case Else
CurStyle.Delete
End Select

Next CurStyle

'Open a new workbook.
Set tempBook = Workbooks.Add

'Disable alerts so you may merge changes to the Normal style
'from the new workbook.
Application.DisplayAlerts = False

'Merge styles from the new workbook into the existing workbook.
MyBook.Styles.Merge Workbook:=tempBook

'Enable alerts.
Application.DisplayAlerts = True

'Close the new workbook.
tempBook.Close

End Sub

Hi, I've used your fun little style killer for some time, but have run into a new wrinkle in an inherited workbook. When I try to run the script I get a VB error

Run-time error '1004':
Delete method of Style class failed

Hi,
I've inadvertently gained some styles from cutting and pasting data into my workbook from another workbook. I've written a macro, no unlike yours that has deleted the majority of the unwanted styles. However, I have 36 unwanted styles left over. I can't delete these in the interface or macro. They have strange names like "LÓÄþÍN^NuNVþˆHÁ
(n 2 2" and the protection: Locked is checked. If I rename them (modify style) it creates another style with the new name that can be deleted but the original persists. As far as I can tell these styles are no in use in the workbook. Any suggestions?