Link to home
Start Free TrialLog in
Avatar of jobprojn
jobprojnFlag for United States of America

asked on

Excel VBA used to delete conditional formatting crashes

Hello Experts.  I have the following piece of VBA code which goes through every third row within a range and deletes the conditional formatting.  It works fine (and is relatively fast) until there's more than a few hundred rows and it crashes.

Any ideas on how I can modify this so as to avoid a crash when running with 500+ rows in the spreadsheet?

Thanks.

Sub DeleteConditionalFormatting()

Dim lLastRow As Long, lRowLoop As Long

    Application.Calculation = xlCalculationManual 'turns off auto calculation until update is complete

        With Worksheets("Cycle Time")
        lLastRow = .Cells(.Rows.Count, "AD").End(xlUp).row
                    
                    For lRowLoop = 10 To lLastRow Step 3

                        With .Cells(lRowLoop, "AD").Resize(1, 5)
                            .FormatConditions.Delete
                        End With

                    Next lRowLoop
                
    Application.Calculation = xlCalculationAutomatic 'turns auto calculation back on
            
    End With

End Sub

Open in new window

Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

First of all, it's not a good thing that what seems like such a simple task causes a crash.

That said, perhaps another approach. Why are you deleting the conditional formatting rule? Have you considered augmenting the rule to include a condition that results in no formatting on every third row? Are you also creating the formatting? If so then only apply it on the rows where you want it.

Kevin
Avatar of jobprojn

ASKER

Thanks for the reply Kevin.  By default we receive a spreadsheet that comes with conditional formatting for all rows.  To make it easier to read we want to remove the formatting for every third row.  

The simplest thing to do would be to remove the formatting for the rows that don't need it, but it's looking like it's less and less of an option.  I wasn't sure if there was another way to delete conditional formatting other than what I'm currently using, or if there was a more efficient way to loop through the rows.
Excel's internal format definition tables are complex and it tries to be economical wherever possible. By removing every third conditional formatting rule you are forcing Excel to split a contiguous format into many smaller parts.

With this in mind, how about removing all conditional formatting for the entire column and then applying your own conditional formatting on the entire column that handles your every third condition.

Kevin
That's good to know.  The only thing that is challenging with your option is the rows that require the conditional formatting are needing the "icon set" type formatting.  With icon set conditional formats I don't believe you can create formulas to specify that the condition only apply to certain rows.  Condition formulas work great for non-icon set conditional formats, but as far as I can tell formulas don't work with icon set formats.
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
These lines:

        .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),3)=0"
        .FormatConditions(1).StopIfTrue = True

add the non-formatting every-third-row rule and tell Excel to stop processing additional conditions if that one is true. So after the above condition you can have as many conditions as you want and they will never be applied on rows 3, 6, 9, etc.

Kevin
Thank you Kevin.