VBA Code running extremely slow

[Cauegr]

Hello good people, I just registered but I have been an anonymous user using this forum for most of all my self-taught VBA life.

This is probably one of the most simple codes i've ever wrote yet it's EXTREMELY slow, and I can't figure out why.
All it does is hide some specific lines on sheets that i need not to appear and would be just too painful to make all of this manually.

Any thoughts on why it's being so slow? When I mean slow I mean it's taking 10min+ to run in 20 sheets with 100-200 rows each.

Excel is 2013 on Windows 8

Thanks in advance!

Sub arrumaorç()
Application.ScreenUpdating = False


For n = 2 To Worksheets.Count - 2


x = 2
    
    Sheets(n).Activate


    Do Until Cells(x, 2).Value = "Total Geral"
    
        If Cells(x, 2).Interior.TintAndShade > 0 Or Cells(x, 2).Interior.Color = 13434828 Or Cells(x, 2).Interior.Color = 10092543 Then
            Cells(x, 2).EntireRow.Hidden = True
            Else: x = x + 1
        End If
    Loop
Next n


End Sub

 

[mole999]

do you have any worksheet change events on the worksheets you are looking at

or

do you have formulas that all recalculate as you hide the rows

 

[Alan_P]

Wouldn't that throw you into an infinite loop if any of those criteria are true?

 

[Cauegr]

Hi, first thank you for your time.

No, there are no change events on the sheets i'm working at, and no formulas recalculating as I hide the lines.
Even knowing this, are there benefits in changing the calculation to manual and disabling events?

 

[Cauegr]

Alan, sorry, I posted the code without the update.

This is actually the functional no infinite loop code.

Sub arrumaorç()
Application.ScreenUpdating = False


For n = 2 To Worksheets.Count - 2


x = 2
    
    Sheets(n).Activate


    Do Until Cells(x, 2).Value = "Total Geral"
    
        If Cells(x, 2).Interior.TintAndShade > 0 Or Cells(x, 2).Interior.Color = 13434828 Or Cells(x, 2).Interior.Color = 10092543 Then
            Cells(x, 2).EntireRow.Hidden = True
        End If
    x = x + 1
    Loop
Next n


End Sub

 

[ukmikeb]

Wouldn't that throw you into an infinite loop if any of those criteria are true?

It would appear so because the row number isn't incremented once the row is hidden!

Edit: It appears that the code has been modified in line with this comment.

 

[Alan_P]

Hi Cauegr,

I tested your code on a test workbook with 50 sheets, 200 rows per sheet with about 50% matching your criteria... it took about 10 seconds (which is slow for what it is doing!)

Is there a criteria behind the cell color that we can check rather than checking the color itself?

Cheers,
Alan.

 

[Cauegr]

Hi Cauegr,

I tested your code on a test workbook with 50 sheets, 200 rows per sheet with about 50% matching your criteria... it took about 10 seconds (which is slow for what it is doing!)

Is there a criteria behind the cell color that we can check rather than checking the color itself?

Cheers,
Alan.

Hey there Alan,

That's weird, maybe it has something to do with the file being in a remote server? Unfortunately the sheet i'm working into wasn't made by me and the criteria used for the divisions was the cell color.

I'll try adding calculation as manual and some other common speed up code line, I'll get back with the results.

I'm really thankful for your time and care with my question.

Edit: Problem solved, I tried disabling events with no change in processing time then tried turning calculation Manual, that worked as a charm, code running in under 5s. I guess i'll have to read more about when formulas recalculate.