Restoring default Styles in Excel

[dae]

I have an Excel (2010) spreadsheet that has been edited by multiple users and as a result I have a large number of styles that have crept into the spreadsheet. Is there a code that can be run to delete all styles except for the Excel default codes?

[yujin]

Hi, dae. You don't need macro to delete unnecessary styles.
Follow the below instruction.

1. Right-click a sheet tab, and then click "Select All Sheets" on the shortcut menu.
2. Right-click a sheet tab again, and then click "Move or Copy".
3. Select "(new book)" from the "To book:" dropdown list.
4. Check "Create a copy" box, and click OK.

The new workbook has only Excel default styles.

[mancubus]

back-up your file before testing:



Sub vbax_61949_restore_default_styles()

Dim styl As Style

On Error Resume Next
For Each styl In ActiveWorkbook.Styles
If Not styl.BuiltIn Then styl.Delete
Next styl

End Sub

[Paul_Hossler]

This is the macro I use to cleanup lots of redundant Styles, HOWEVER I've had workbooks that were so bad (more styles than Excel could handle), the macro wouldn't work and I had to edit the XML to get rid of 40K+ styles. Ugly



'ALL user defined styles, leaves formatting
Sub DeleteUserDefinedStyles()
Dim oStyle As Style
Dim iStyle As Long
For iStyle = ActiveWorkbook.Styles.Count To 1 Step -1
Application.StatusBar = "Checking Style '" & ActiveWorkbook.Styles(iStyle).Name & "'"
On Error Resume Next
If Not ActiveWorkbook.Styles(iStyle).BuiltIn Then
ActiveWorkbook.Styles(iStyle).Delete
End If
On Error GoTo 0
Next
End Sub

[dae]

Thank you everyone for your input. Paul, thanks for the code. It did the job.