Originally Posted by
Zack Barresse
Moved from post:
Depending on what version of Excel you're using, the FileSearch method was removed in around version 2010 or so. Also, the code you have hard-codes the last row of 65536, which is the limitation of XLS files, not of the new file types, which is over 1M rows. What version of Excel are you using? Also, I don't see the error line of code in the bit you quoted. You'd need to post all of the code you're actually using.
I use an add-in, located at CodePlex, for removing styles:
HTH
Thanks for the quick response!
Yes, I'm using Excel 2010.
The entirety of the code is:
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
[a1048576].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
Also, I attempted to use the tool you linked and haven't been able to get it to work. I used the one listed for versions 2007+ (xlam file). Not sure why but it doesn't seem to give any sort of message or notifications when I attempt to open or run it.
Thanks!