VBA Code running extremely slow

Cauegr

New Member
Joined
Oct 2, 2015
Messages
14
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!

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
            Else: x = x + 1
        End If
    Loop
Next n


End Sub
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 8, 2014
Messages
596
Wouldn't that throw you into an infinite loop if any of those criteria are true?
 

Cauegr

New Member
Joined
Oct 2, 2015
Messages
14
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

New Member
Joined
Oct 2, 2015
Messages
14

ADVERTISEMENT

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

This is actually the functional no infinite loop code.

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

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
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.
 
Last edited:

Alan_P

Well-known Member
Joined
Jul 8, 2014
Messages
596
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

New Member
Joined
Oct 2, 2015
Messages
14
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top