Remove Unwanted Styles from Excel workbook-CodePudding

My excel workbook has come upon a limit for excel Styles. I found VBA code to RemoveTheStyles that were not BuiltIn, applied it and found the Styles were BuiltIn and "SAPBEXstdItem*".

Running this code sadly had no effect. There are approx 36000 styles that got added about 2 weeks ago. I don't know the styles were applied. If I knew how they were created I would try and remove the Style manually but a VBA solution would be preferred.

Regards Peter

Sub RemoveTheStyles()
 
    Dim style               As style
    Dim l_counter           As Long
    Dim l_total_number      As Long
 
    On Error Resume Next
 
    l_total_number = ActiveWorkbook.Styles.Count
    Application.ScreenUpdating = False
 
    For l_counter = l_total_number To 1 Step -1
    
        Set style = ActiveWorkbook.Styles(l_counter)
        
        If (l_counter Mod 500 = 0) Then
            DoEvents
            Application.StatusBar = "Deleting " & l_total_number - l_counter   1 & " of " & l_total_number & " " & style.Name
        End If
        
        If Not style.BuiltIn Then style.Delete
        If Left(style.Name, 13) = "SAPBEXstdItem" Then
            style.Delete
        End If
        Debug.Print style.Name
    Next l_counter
 
    Application.ScreenUpdating = True
    Application.StatusBar = False
    Debug.Print "READY!"
    
    On Error GoTo 0
End Sub

CodePudding user response：

You shouldn't use a code word like style as variable name.

This works for me

Dim sty As Style  '-- don't use style as variable name
For Each sty In ThisWorkbook.Styles
    If sty.BuiltIn = false then 
        sty.delete
    ElseIf sty.Name Like "SAPBEX*" Then 
        sty.delete
    End If
Next