For Each Loop (Make it Faster)

Hello Experts,
I've searched through this Forum's threads about speeding up the For Each....Next loop. There were mentions about variants, LBound, UBound, GoTo, Special Cells, Arrays, storing it in temp memory, etc but I'm unsure how to apply them into my code.

I have 3,000+ rows. Rather than cycling through each cell and performing a command (hide row), is there a faster way to hide entire row if all cells within column A's range with the value of "Hide"? This is code currently takes 6 minutes to cycle through 3,000+ cells, one at a time.

Please Login or Register  to view this content.

Thanks in advance,
Ricky

Depending on the version of excel you are using the simplest thing you can do to speed things up is to turn off auto calculation while running this macro. You can do this manually or via code. Currently each time you hide a row the sheet will recalculate. This takes a long time.

Please Login or Register  to view this content.

JimBobBowie

Originally Posted by sweep

I don't think so, because the op is only looking to hide rows where column A="hide"

Thank-you sweep,
Yes, the "Hide" criteria is down column A for now. I've defined use the dim rng as range in case users inserts new column(s) in the future. However, the rest of my code requires a Data Query of another application. If filtering is used, the refresh doesn't work for the hidden rows. I'm trying to stay away from it for this process.

What's an alternative to Select all cells within range with "Hide" then hide entire row all at once?

I appreciate your help and everyone's participation.

Regards,
Ricky

Originally Posted by JimBobBowie

Depending on the version of excel you are using the simplest thing you can do to speed things up is to turn off auto calculation while running this macro. You can do this manually or via code. Currently each time you hide a row the sheet will recalculate. This takes a long time.

Please Login or Register  to view this content.

JimBobBowie

Thank-you JimBobBowie,
Yes, I have trying turning off calculation and screenupdating also. But it's still slow. I think it's because the code inherently is slow because it goes down the column one cell at a time and evaluates whether or not it's a "Hide".

Regards,
Ricky

Originally Posted by sweep

Try this

Please Login or Register  to view this content.

Thank-you sweep!
I have tested the code without any alterations but it hid the entire range (all rows to 30,0000). Could you please assist?

I appreciate it very much.

Regards,
Ricky

Thank-you skywriter,
It works nicely.

Looks like filtering is the way to go, also as suggested by sweep. I'm glad your code turns off the filtering once all of the "Hide" identifier rows are hidden. That way, my data query to another application would still work when refreshing my Hidden rows.

Offset (1) means down one row. But what's the Count - 1 do? I plan on naming the ranges in case future users may insert rows or columns befor "A1". So I wonder if I could just leave this line code as is.

Please Login or Register  to view this content.

Thanks very much.

Regards,
Ricky

Thank-you skywriter.
Makes perfect sense.

Regards,
Ricky