Delete custom styles in Excel (VBA)

If your workbook has too many 'custom' styles and is taking forever to delete them one-by-one, why not delete them all at once with the help of macro in 5 simple steps.

The macro will not delete the 'built-in' styles such as shown below.

Step 1

In excel, press Alt + F8. This will bring up the Macro dialog.

Step 2

Type in a name for the macro, let's give it "DeleteCustomStyles" although you can use other name you see fit.

Step 3

Then press Create. This will bring up the VBA window.

Step 4

Type in (or just copy and paste) the following code into the Sub region.

Note that the first and the last lines are already in the VBA window.

Sub DeleteCustomStyles()

    Dim iStyle As Style

    On Error Resume Next

    For Each iStyle In ActiveWorkbook.Styles

        If Not iStyle.BuiltIn Then

            iStyle.Delete

            Application.StatusBar = "Custom style " & iStyle.Name & " deleted"

        End If

    Next iStyle

    Application.StatusBar = False

    MsgBox "All custom styles have been deleted."

End Sub

Your code should look like this.

Step 5

Close the VBA window (Alt + Q), go back to excel, and press Alt + F8 again.

Select the "DeleteCustomStyles" macro and then hit Run.

If your workbook has about 100+ customs styles, it will only take a few seconds to finish. But if your workbook has so much, like 50,000+ custom styles, it may takes about 10 to 20 minutes to delete all the custom styles.

So if your workbook has so many custom styles, just sit back and relax while the macro delete the unwanted custom styles.

~Cheers