Results 1 to 1 of 1

Thread: Remove Unused Custom Styles VBA

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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

    Word of caution. Please create a backup of your file before trying this code.




    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eekDyfS0 CD
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eevG7txd 2c
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg.9dPo-OdLmZ09dc21kigjmr
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg.9cXui6zzkz09cZttH_-2Gf
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-13-2023 at 10:31 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. How to Add Custom List And Use in Custom Sorting
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 06-07-2013, 10:41 PM
  2. LTRIM() Function VBA: To Remove Spaces
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  3. Removing unused Cell styles - need an efficient code
    By siddharthsindhwani in forum Excel Help
    Replies: 8
    Last Post: 04-15-2013, 07:12 AM
  4. How To Make A Custom VBA Function Available In All Workbooks
    By Safal Shrestha in forum Excel Help
    Replies: 2
    Last Post: 04-11-2013, 02:01 PM
  5. Delete Remove Rows By Criteria VBA Excel
    By marreco in forum Excel Help
    Replies: 5
    Last Post: 12-20-2012, 05:56 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •