VBA Express : Excel - Delete styles collection

Excel

Delete styles collection - active workbook

Ease of Use

Easy

Version tested with

2000

Submitted by:

johnske

Description:

On running the code, all styles except 'Normal' will be deleted from the active workbook. (Caveat: This will not work in a workbook that has any sort of protection applied)

Discussion:

Often, when things are copied from workbook to workbook all the styles in the first workbook are incorporated in the new workbook. When this is done many times it can reach a the point where you simply cannot format cells any more. Microsofts knowledge base contains a procedure that is supposed to clear all these styles, but it completely fails when previous users have used a weird 'name' for a style that can't be recognized by Excel as being the name of a style, and this is common. An example of such an error is for users to use a number format as a 'name' for the style, this will cause MS's procedure to 'crash and burn'. The following procedure will delete almost all of those styles, however, note that there may be a couple of even weirder styles that it can't delete e.g. if a user uses =(a hyperlink or path) for the 'name' of a style. - (But it won't crash when these names are met)

Code:

instructions for use

			Option Explicit 
 
 'Deletes All Styles (Except Normal) From Active Workbook
Sub ClearStyles() 
    Dim i&, Cell As Range, RangeOfStyles As Range 
    Application.ScreenUpdating = False 
    Application.EnableEvents = False 
     'Add a temporary sheet
    Sheets.Add before:=Sheets(1) 
     'List all the styles
    For i = 1 To ActiveWorkbook.Styles.Count 
        [a65536].End(xlUp).Offset(1, 0) = ActiveWorkbook. _ 
        Styles(i).Name 
    Next 
    Set RangeOfStyles = Range(Columns(1).Rows(2), _ 
    Columns(1).Rows(65536).End(xlUp)) 
    For Each Cell In RangeOfStyles 
        If Not Cell.Text Like "Normal" Then 
            On Error Resume Next 
            ActiveWorkbook.Styles(Cell.Text).Delete 
            ActiveWorkbook.Styles(Cell.NumberFormat).Delete 
        End If 
    Next Cell 
     'delete the temp sheet
    Application.DisplayAlerts = False 
    ActiveSheet.Delete 
End Sub

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Insert/Module
Copy and paste the code into the Module
Now select File/Close and Return To Microsoft Excel
Save your work

Test the code:

Select Tools/Macro/Macros.../ClearStyles/Run
After running the code, go to Format/Styles... and check that the styles have been deleted
NOTE: To apply this to a number of workbooks other than the active book, look at http://www.vbaexpress.com/kb/getarticle.php?kb_id=368

Sample File:

ClearStyles.zip 9.06KB

Approved by mdmackillop

This entry has been viewed 101 times.