Remove Unused Custom Styles VBA

**Admin** · 08-23-2012, 02:32 PM

Hi All,

To remove unused custom styles from a workbook.

Code:

Sub RemoveUnusedStyles()
    
    '// Author  : Admin @ ExcelFox.com
    '// Purpose : Delete all unused styles from a workbook.
    
    Dim i   As Long
    Dim c   As Long
    Dim n   As Long
    Dim r   As Long
    Dim d   As Object
    Dim s   As Style
    Dim a
        
    Set d = CreateObject("scripting.dictionary")
        d.comparemode = 1
    
    With ThisWorkbook
        n = .Styles.Count
        'get all the non-built styles
        For i = 1 To n
            If Not .Styles(i).BuiltIn Then
                d.Item(.Styles(i).NameLocal) = False
            End If
        Next
        
        n = 0
        For i = 1 To .Worksheets.Count
            With .Worksheets(i).UsedRange
                For c = 1 To .Columns.Count
                    For r = 1 To .Rows.Count
                        Set s = .Cells(r, c).Style
                        If Not s.BuiltIn Then
                            'match cell style with the style collections
                            If d.exists(ThisWorkbook.Styles(s.Name).NameLocal) Then
                                d.Item(ThisWorkbook.Styles(s.Name).NameLocal) = True
                            End If
                        End If
                    Next
                Next
            End With
        Next
        a = Array(d.keys, d.items)
        For i = LBound(a) To UBound(a(0))
            'delete unused styles
            If Not CBool(a(1)(i)) Then
                .Styles(a(0)(i)).Locked = False
                .Styles(a(0)(i)).Delete
            End If
        Next
    End With
    
End Sub