Other Issues - Deleting All Custom Cell Styles

Is there a way for me to delete all custom cell styles in a Excel 2007 workbook, without
having to right-click on each one and delete it individually?  When I paste
in data which has been exported from other programs (particularly SAP) I seem
to end up with hundreds of different cell styles which are unnecessary and
annoying for me to scroll through to get down to the automatic cell styles
which I generally use.
Any suggestions gratefully received.

• Select the cells for which you need to remove the styles, or the whole sheet, 
• Click Cell Styles under the Home tab.
• Under the Good, Bad, and Neutral section. Select normal. 

Hi

This excel macro should help. You will need to save and close the workbook for the changes to take affect.

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

Hi Chris,

Thank you for posting but there is one major problem to overcome - ME!

Never run or had any knowing use of a macro in my life - sad but true........

I'll have a look around the forum and see if I can get some help at running you script in 2007.

Thanks for your efforts and keep your fingers crossed for me.

G

Hi Graham,

1) Right click on the worksheet (eg "Sheet 1").

2) Click on View Code - this will bring up a Visual Basic code screen for which you can type a macro code. You don't need to type the code...just copy and paste whatever code was sent to you. ie

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

3) Save your file as a Macro-Enabled Excel File

4) Close the file

5) Reopen the Macro-Enable Excel file

6) Go to View -> Macro ->View Macros

7) Click on Run

All cell styles should be gone without affecting your cell formatting. Worked for me!

Thanks to whoever sent that macro code through :D

Jo :)