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

 

[delaneyjm]

Have you tried reseting Excel to first run condition?

 

[Spaniel]

Excel 2007

 

[delaneyjm]

You could try the following macro that will delete all non-built in styles.

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

 

[Spaniel]

Absolute legend. Thank you so much.

 

[jdminton]

I found this code to work sufficiently (and maybe a little faster)


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

 

[XLGeek]

This thread covers both secenarios:
1) the best and fastest OOXML based approach
2) optimized for perfomance VBA algorithm if you want (and insist) on living in the past

http://social.msdn.microsoft.com/Forums/hr-is/exceldev/thread/446f388f-5eae-4799-ac8e-84330a6a5d4d

 

[mdubdroid]

5 years later....worked exactly as i needed it to

thanks!

 

[sanunes]

You could try the following macro that will delete all non-built in styles.

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

Ok. It´s work fine!

 

[CA_likes_excel]

You could try the following macro that will delete all non-built in styles.

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

This worked fabulously! It is going to save my files!

Just a note for future users: after starting the macro running, I went over to chrome, then back to excel. Excel went kinda-gray, and was indicating it was not-responding. I let it continue this way, figuring it was working so hard it triggered the not-responding warning. After about 10 minutes, excel came back as normal, and my file was fixed!

I was unsure of how this macro worked, so I had run it as a test. I wasn't sure if it would delete all formatting, or just these mystery formats. The formatting I had (and wanted) on my pages remained, and the 650,000 other styles are now gone.

THANK YOU delaneyjim! You have saved my files.