Excel 2007 Remove all Custom Styles

[Spaniel]

Hi folks,

A fairly complex excel file I have has decided to play tricks and has set the default style to currency. Now I know the way to reset this is to select the 'normal' style in the list, and change this back to 'general', however I don't have a 'normal' style. All the default ones have been replaced with hundreds (and I mean hundreds - I tried deleting them one by one for a while) of custom styles with this currency format.

Can someone help me to reset this file so that I can see this 'normal' again? Or how to delete all custom styles?

Steps taken so far:

• Deleted hundreds of custom styles click by click - This got rid of some, there are still more.
• Open and repair document (assuming it was corrupted) - this did nothing
• Pulled my hair out - This made me feel better

Thanks in advance.

Sam

 

[Horrgakx]

Hi - thanks for this, I was looking for a solution. However when I try to put this into a Macro I'm told "The following features cannot be saved in macro-free workbooks: VB Project". I then choose to continue saving a macro free workbook but Excel locks up.

I think it's because I'm in a corporate environment and maybe I can't change stuff. But I'm left being unable to even put lines round cells or formatting cells as dates because of the error message.

 

[RoryA]

If you put code in a workbook, you can't save it in xlsx format, you have to use xlsm or xlsb (or the older xls) formats. However, you don't need to keep the code in the workbook, you just need to run it once.

 

[Horrgakx]

Thanks for this - I've never used macros before so I appreciate your help.

For the record, this script below exited with an error and just didn't work;

Sub StyleKill()
Dim styT As Style
Dim intRet As Integer
For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then styT.Delete
End If
Next styT
End Sub

However this script below worked fine, so thanks very much Once done I confirmed the spurious formatting crap had gone and saved it back as a .XLSX file.

Sub StyleKill()
     Dim styT As Style
     Dim intRet As Integer
     On Error Resume Next
     For Each styT In ActiveWorkbook.Styles
         If Not styT.BuiltIn Then
             If styT.Name <> "1" Then styT.Delete
         End If
     Next styT
 End Sub

 

[Horrgakx]

UPDATE - I would edit my post above but for whatever reason there's no EDIT button.

Although it did get rid of a lot of the formatting rubbish at the top of the Styles window, they are still present under "Titles and Headings" and although I could make some changes to the spreadsheet I'm now being given the same error as before.

 

[RoryA]

For the record, this script below exited with an error and just didn't work;

What was the error?

Did you close the workbook and reopen it after running the code and saving?

 

[Horrgakx]

It stopped on one of the lines and highlighted it, I can't remember what it said sorry.
At that point I tried the second code which worked, I didn't try to close and re-open with the first code.