Results 1 to 2 of 2

How to Delete Cell Styles

Threaded View

  1. #1
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    How to Delete Cell Styles

    I have attached a workbook that shows some excel cell styles that I simply cannot delete manually or via vba.

    I use the default cell styles (input, calculation etc etc) because I like to keep my models as clean and organized as possible. When there are 10 rogue/phantom cell styles I can't delete it drives me crazy.
    I have a piece of code I run to clear the cell styles (as this file originally had about 65,000 cell styles!!!) but for some reason it will NOT clear the remaining.
    Can anyone help?

    Code used to purge non default cell styles:

    Option Explicit
    
    Sub StyleKill()
    'this is designed to clean all the cell styles out of a workbook because I prefer to use the DEFAULTS and any custom styles are put above the defaults
        
        'declare variables
        Dim styT As Style
        Dim intRet As Integer
        Dim count As Long
        Dim counter As Long
            
        'count how many styles there are, mostly for LOLz
        For Each styT In ActiveWorkbook.Styles
            count = count + 1
        Next styT
        
        'ask if you want to delete all styles (but it means all NON default styles)
        intRet = MsgBox("You have " & count & " styles to delete. Do you want to Delete?", vbYesNo)
            
            If intRet = vbYes Then
                On Error Resume Next ' error handling
                    counter = 1 ' used ONLY for the statusbar
                    For Each styT In ActiveWorkbook.Styles
                    Application.StatusBar = "Deleting cell style [" & counter & " of " & count & "]"
                        If Not styT.BuiltIn Then
                        'MsgBox styT.Name
                            'styT.IncludeProtection = False ' attempting to unlock the style BEFORE deleting but I am not sure it is working
                            styT.Delete
                        End If
                        counter = counter + 1
                    Next styT
            End If
            
            Application.StatusBar = False
            
    End Sub
    Attached Files Attached Files
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How do I delete styles?
    By Dave H in forum Excel General
    Replies: 18
    Last Post: 02-13-2015, 04:56 PM
  2. Too much cell styles need to delete by VBA
    By terryliu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2015, 02:37 AM
  3. Replies: 1
    Last Post: 03-08-2013, 02:05 PM
  4. [SOLVED] Delete all non-custom styles
    By AlvaroSiza in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-04-2013, 07:27 PM
  5. How to delete cell styles?
    By meyestone in forum Excel General
    Replies: 7
    Last Post: 01-11-2012, 04:56 PM
  6. Excel 2007 : Delete Built-in Cell Styles
    By miroth in forum Excel General
    Replies: 1
    Last Post: 04-30-2010, 12:04 AM
  7. Macro that will delete any un used styles.
    By Tom in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2005, 03:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1