Delete styles from an Excel workbook, any non-standard style will be deleted. Used styles in Excel 2016 (Excel 365).
Inheriting tools from other developers, I ended up most of the times with lots of styles (due to copy and paste across workbooks) and that could not only add size that can be freed, or affecting the performance. below is how to delete all styles that are not standard.
Sub Styles_DeleteNonStandard(Optional WB = "This")
If Wb = "This" Then WB= ThisWorkbook.name
StyStd = ""
StyStd = StyStd & ""
StyStd = StyStd & "20% - Accent1||20% - Accent2||20% - Accent3||20% - Accent4||20% - Accent5||20% - Accent6||"
StyStd = StyStd & "40% - Accent1||40% - Accent2||40% - Accent3||40% - Accent4||40% - Accent5||40% - Accent6||"
StyStd = StyStd & "60% - Accent1||60% - Accent2||60% - Accent3||60% - Accent4||60% - Accent5||60% - Accent6||"
StyStd = StyStd & "Accent1||Accent2||Accent3||Accent4||Accent5||Accent6||Bad||Calculation||Check Cell||Comma||"
StyStd = StyStd & "Comma [0]||Currency||Currency [0]||Explanatory Text||Good||Heading 1||Heading 2||Heading 3||"
StyStd = StyStd & "Heading 4||Input||Linked Cell||Neutral||Normal||Note||Output||Percent||Title||Total||Warning Text"
For Each Sty In Workbooks(WB).Styles
If ANStrSearch(Sty.Name, StyStd, "||") = 0 Then
Sty.Delete
End If
Next
End Sub
If Wb = "This" Then WB= ThisWorkbook.name
StyStd = ""
StyStd = StyStd & ""
StyStd = StyStd & "20% - Accent1||20% - Accent2||20% - Accent3||20% - Accent4||20% - Accent5||20% - Accent6||"
StyStd = StyStd & "40% - Accent1||40% - Accent2||40% - Accent3||40% - Accent4||40% - Accent5||40% - Accent6||"
StyStd = StyStd & "60% - Accent1||60% - Accent2||60% - Accent3||60% - Accent4||60% - Accent5||60% - Accent6||"
StyStd = StyStd & "Accent1||Accent2||Accent3||Accent4||Accent5||Accent6||Bad||Calculation||Check Cell||Comma||"
StyStd = StyStd & "Comma [0]||Currency||Currency [0]||Explanatory Text||Good||Heading 1||Heading 2||Heading 3||"
StyStd = StyStd & "Heading 4||Input||Linked Cell||Neutral||Normal||Note||Output||Percent||Title||Total||Warning Text"
For Each Sty In Workbooks(WB).Styles
If ANStrSearch(Sty.Name, StyStd, "||") = 0 Then
Sty.Delete
End If
Next
End Sub
WB
Views 3,581
Downloads 1,432
CodeID
DB ID