VBA optimization with a "For Each"

[Oppressed1]

VBA optimization with a "For Each"

I want to check rows 50,000 and greater for some cell background color on a 60,000 row sheet. Right now I essentially go

Please Login or Register  to view this content.

Now it actually spins for a couple of seconds just getting up to line 50000. I'd like to improve that. I already see that
dim wksht as worksheet
and substituting wksht for Worksheets(i) is useful, since my belief is that the "high bound" of the For statement is reevaluated every single time.

I could also put "With c" above the if statement, but the goal here is to speed the
For Each
If
end if
next
construct so a "With" there would seem to actually slow the interpretive processor. (Side note: I'm not clear whether snail-paced VBA is actually "compiled." I suspect that the "debug/compile" option really is nothing but a syntax preprocessor. Elucidation welcome.)

An obvious possibility is to instead of wksht.UsedRange define a range of the specified row, column A, to the end of the worksheet. I'm not sure how I'd code that; for one thing, it may not be on the active sheet, so ActiveCell.SpecialCells(xlLastCell) or ActiveCell.SpecialCells(xlLastCell).Row doesn't work per se. I'm interested in how it would be coded, yet note that iStartRow may be row 1 in some cases, not necessarily 50000. Any solution would need to be to optimize both situations.

TIA for advice.

[StevenM]

There might be a faster way, but this should be better than what you had.

Please Login or Register  to view this content.

[JBeaucaire]

A little tighter:

Please Login or Register  to view this content.

To add that into your worksheets scenario:

Please Login or Register  to view this content.

[Oppressed1]

Steven: thanks so much. Happily, set rgLast = wksht.Range("A1").SpecialCells(xlCellTypeLastCell).row
is valid when not referencing the active sheet, so that's handy.

Can you really range from A1 to the end row? I had problems testing that. Or should I use IV a la

Set rg = Range("A1" , "IV" & worksheets(2).Range("A1").SpecialCells(xlCellTypeLastCell).row)

Or maybe I should use wksht.Range("A1").SpecialCells(xlCellTypeLastCell).column instead of "IV"
but would I need the nasty conversion to alpha?

How about range(cells(1,1), cells(a,b)) where a and b are the long expressions shown above?

Thanks again.

[Oppressed1]

Ignore that "IV" rambling, Steven. You were actually directly referencing the last cell, not the lastcell's row. I misread, sorry. JBeaucaire's post got me thinking straight on that aspect - thanks for both of your suggestions.

[Oppressed1]

I suspect that repeated reevaluation of loop "prongs" - or one of them - is part of VBA's slowness. I started out with "for each c in usedrange" because that seemed to have the best chance of NOT being reevaluated. On the other hand, maybe "for each" never reevaluates. Thoughts on that are welcome.

Also, I've long coded things like

Please Login or Register  to view this content.

in the belief that the "upper" bound of loops is always reevaluated and that the "lower" is not. I also work to simplify the upper bound in situations where reversing the parms and using Step doesn't do the job.

[JBeaucaire]

I believe you're overthinking this whole issue. The FOR/NEXT syntax is designed to loop through a series without repeating. It's inherent.

[JosephP]

the bounds are not re-evaluated. run

Please Login or Register  to view this content.

and see that it does not end up in an infinite loop. the slow speed compared to the new versions is simply that they aren't testing 50000 irrelevant rows.

[Oppressed1]

I believe you're overthinking this whole issue. The FOR/NEXT syntax is designed to loop through a series without repeating. It's inherent.

Not sure what you mean by inherent. To really exaggerate

Please Login or Register  to view this content.

The above should *not* be run by anyone whose infinite loop flower is unspoiled. Okay, okay, this is cheating, warping the loop variable, not the bounds.

I may get your point, because

Please Login or Register  to view this content.

apparently does NOT reevaluate, ergo is not an infinite loop. Testing that just now surprised me. Okay, maybe I'm too paranoid.

BTW I'm not aiming to avoid infinite loops or reprocessing within the loops. I'm just musing whether loop bounds are reevaluated, in the interest of optimizing every gosh durn cycle that I can.

[shg]

Changing the loop control variable inside the loop is a cardinal sin. There is never a good reason for it.