[Tags: guide, excel, accounting]
OPTION 1: The macro below will show a prompt "Delete Style ____" for each style which and you will need to click "YES" for each style. If you are not concerned about deleting a valid style you can use OPTION 2
Sub DeleteStyles()
'
' DeleteStyles Macro
'
Dim styT As Style
Dim intRet As Integer
For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then
intRet = MsgBox("Delete style '" & styT.Name & "'?", vbYesNo)
If intRet = vbYes Then styT.Delete
End If
Next styT
End Sub
OPTION 2:The macro below will delete all unused styles without a prompt. This is faster but can be more dangerous than OPTION 1. Make sure you save the file both before and after running the macro (thanks to Nick)
Sub DeleteStyles()
'
' DeleteStyles Macro
'
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
OPTION 3: For those that do not want to deal with Macro's there is a tool you can download that I also find very helpful and easy to use. The tool removes unused cell styles, "stubborn styles" that you can't delete through Excel UI, bad named ranges, named ranges with external references, and unhides non-system created hidden named ranges. Download XLCleaner here: https://skydrive.live.com/?cid=53e1d37f76f69444&id=53E1D37F76F69444!526&sc=documents
- Related Articles:
- Audit Shortcuts for Excel
- Bank of England provides best historical foreign exchange rates data
- Free Microsoft Excel Master Help Provided
Reader Comments (Page 1 of 2)
Sunday February 27, 2011 @ 02:29 PM
Not comment
Tuesday March 8, 2011 @ 04:42 PM
It works
Wednesday April 6, 2011 @ 08:37 AM
Thanks TJ. I managed to overcome the problem
Tuesday August 9, 2011 @ 08:27 AM
It's taking forever!! been waiting for it to finish for over 30 mins, still going. Have to hold the enter key with a mobile to be able to keep it going. I don't know what style is deleting as it's just numbers and no description on from what tab or how long would take. Would give some more feedback once is finished... if it ever does.
Tuesday August 9, 2011 @ 12:08 PM
I had to stop it through task manager since there were so many cell formats that it kept asking "Delete style"... 2 hours wait :( OOOOhhh, not sure what to do now.
Friday September 23, 2011 @ 03:51 PM
Worked Liek a charm
Wednesday November 2, 2011 @ 10:51 AM
Thanks
Tuesday November 8, 2011 @ 05:18 PM
If you don't want to push enter after every style then use the following code. It took about 5-10 minutes to complete on my pc.
Sub DeleteStyles()
'
' DeleteStyles Macro
'
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
Wednesday November 16, 2011 @ 01:42 AM
RE: Did it take 5-10 minutes even with holding down enter key?
Wednesday November 23, 2011 @ 12:26 AM
Why do I encounter 400 error when I copied the code in the workbook and run it?
Monday January 23, 2012 @ 02:19 AM
Everytime i want to run it it comes out with macro error and if I click on debug, it highlights on "styT.Delete". Any way to fix this bug
Friday March 9, 2012 @ 10:18 AM
Worked wonderfully
Monday March 26, 2012 @ 09:35 AM
An easier way to solve the problem is to just save your workbook in CSV and then re-open it in XL, resave in XLS format. This might be annoying if you have too many worksheets in the workbook (since you will have to save each single worksheet), but it will solve the problem.
Thursday July 26, 2012 @ 02:20 PM
Thank you, thank you thank you! This macro worked perfectly. I can't wait to zap all the extraneous styles from the rest of my workbooks now.
Thursday August 2, 2012 @ 09:39 AM
Dave, How is saving a 5 sheet workbook to CSV, then reopening it in Excel, Saveas, then reconstructing the workbook "easier" than running a 10-line macro?
Saturday November 17, 2012 @ 04:05 PM
Rich, +1
Thursday March 7, 2013 @ 09:16 AM
TJ; Thank you ..Thank you...Thank you. I was having a hell of a time trying to convert Excel 2010 files to be compatible with Excel 2003 users for months until I got your code. This is by far the best piece of code I have ever came across. You are a life saver.
Tuesday April 16, 2013 @ 12:42 PM
How do you run a macro? I'm new to Excel
Wednesday May 29, 2013 @ 09:44 AM
OK - seems I'm alone in this but the macro ran perfectly - only a handful of styles deleted - Still having the problem.
(No visual change to the format of the Excel document though)
Have also tried the CLcleaner file - again ran perfectly - did some deleting etc - but no affect on error - still occurring.
Its a multi-worksheet document - do i need to run it again on multiple sheets?
Saturday November 6, 2010 @ 07:36 AM
TJ .. very usefull tip.
txs