this has to be the stupidiest error ever..."too many formats!" but CAN'T DELETE ANY FORMATS...

[sadams1]

hello All,
As you may be able to sense, the level of frustration with this is off the charts...I've read through the posts here & elsewhere online but have not found a way around this one. From what I've found, there's a "4,000 limit" to the number of formats...OK...I've been diligent about streamlining as much of the formatting in a large workbook as possible, there are no "conditional formats", I've deleted multiple sheets thinking this would take down the number of formats but nope!
What on God's green earth is this???

 

[Alex Blakenburg]

hello Alex
Appreciate the feedback! Updated profile & ran performance & "custom styles". There are LOTS of styles.

When you ran the Performance Check did you then try the "Optimize all" button ?
If you did and it didn't help, then on a copy of your workbook try the macro below:

Note: I have not used this in a very long time and it probably needs a tidy up but give it a try.

Sub StyleKill()
' Deleting Unwanted Styles
    Dim styT As Style
    Dim intRet As Integer
    Dim intCnt As Long
    Dim totStylesCnt As Long
    Dim oldStatusBar As Boolean
    Dim strMsg As Long
    Dim i As Long
       
    'store the status bar setting
    oldStatusBar = Application.DisplayStatusBar
    'display the status bar
    Application.DisplayStatusBar = True
    
    If MsgBox("Are you sure?", vbYesNo + vbDefaultButton2, "Confirm macro") = vbNo Then Exit Sub
    
    totStylesCnt = ActiveWorkbook.Styles.Count
    
    With ActiveWorkbook
        For i = totStylesCnt To 1 Step -1
            If Not .Styles(i).BuiltIn Then
                
                On Error Resume Next
                .Styles(i).Delete
                On Error GoTo 0
            
                intCnt = intCnt + 1
                
                If intCnt Mod 500 = 0 Then
                    'display a message
                    strMsg = "Total No of Styles " & totStylesCnt & " - Deleted " & intCnt & " Styles"
                    Application.StatusBar = strMsg
                End If
                
            End If
    
        Next i
    End With
    
    MsgBox "No of Styles Removed:- " & intCnt
       
    'remove any text in the status bar area
    Application.StatusBar = False
    'reset the status bar to the user's preference
    Application.DisplayStatusBar = oldStatusBar
    
End Sub

 

[MARK858]

If Alex's code doesn't sort it, what happened when you ran the program in post 6?

 

[sadams1]

hello Mark,
If you're referring to the code right above your last post, it's gives the following error (see screenshot)

 

[MARK858]

No I'm referring to the program to download in the link in post 6

 

[Joe4]

hello Mark,
If you're referring to the code right above your last post, it's gives the following error (see screenshot)

Change this line:

Dim strMsg as Long

to this:

Dim strMsg as String

as it is returning a message, not a number.

 

[sadams1]

hello Joe,
Updated the code, ran it & it's still running...status shows "deleting 15,000 styles" or something...I get the sense this is an "infinite loop"

 

[Joe4]

hello Joe,
Updated the code, ran it & it's still running...status shows "deleting 15,000 styles" or something...I get the sense this is an "infinite loop"

It is not my code, so I cannot comment about it. I was just pointing out the coding error.
But if you have over 64,000 formats, I imagine it could take a really long time to run.

 

[Micron]

I get the sense this is an "infinite loop"

I take it that you didn't step through it for at least one loop to be sure that it would not get stuck in a loop without giving any indication if that happened. Always a good idea to do that with untested code IMHO. I agree that it could be functioning correctly but there's just a lot of work being done. I'd give it 30 minutes before worrying about it.

 

[sadams1]

hello Micron,
I have no experience with stepping through code otherwise I would definitely would do so. I understand setting up breakpoints to run chunks but don't see places in the code to do so.

 

[Joe4]

hello Micron,
I have no experience with stepping through code otherwise I would definitely would do so. I understand setting up breakpoints to run chunks but don't see places in the code to do so.

One easy way is to put a breakpoint near the beginning of your code. Then when you run it, it will stop there.
You can then use the F8 key to proceed through the code one line at a time, so you can see what is happening.

See here for more details: Debugging in Excel VBA
There are tons of other videos and tutorials on these debugging methods.