Avatar of jobprojn
jobprojn
Flag 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

Microsoft Excel

Avatar of undefined
Last Comment
jobprojn

8/22/2022 - Mon
zorvek (Kevin Jones)

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
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.
zorvek (Kevin Jones)

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
jobprojn

ASKER
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
zorvek (Kevin Jones)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
zorvek (Kevin Jones)

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
jobprojn

ASKER
Thank you Kevin.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.