VBA that hides rows with 0 takes FOREVER to run

**Sky188** · 02-14-2013, 07:49 PM

Good evening all,

I've got a fairly simple code (see below), and it takes absolutely forever to fully run (I'm personally looking at ~4 minutes every time).

Please Login or Register  to view this content.

My ultimate purpose is I have other sheets that determine if the values in column G are 0, or are greater than 0, and if that value is 0, I want to hide the rows. The above code accomplishes the task, but takes a very long time.

Is there a better, more efficient way to go about this?

**mat.davies** · 02-14-2013, 08:07 PM

Hi

Having a bit of trouble replicating this, on my fairly slow laptop, processing the ~4,500 cells takes 1.04s. It goes up to 78s if i extend it to 64000 cells.

It kind of depends what's causing the slow behaviour. If you're hiding a large no of consecutive rows, you could get it to delay the hide operation until it comes across a row that doesn't need hiding.

For my example this was the case, so the following code (which groups the rows to be hidden) produced a massive improvement

Please Login or Register  to view this content.

If you are, for example, hiding every other row then you might need to look at building a selection and hiding them all at one, I think the main aim should be to use .hidden = true as little as possible

Cheers

Mat

**Sky188** · 02-15-2013, 06:09 PM

Mat, your above code worked absolutely perfectly, actually.

Thanks a ton!

Is there any way to apply this same code-type to a Sheet Updating scenario, rather than an activate? I'll elaborate below the below code.

Please Login or Register  to view this content.

To label my sheets, the above post by mat fixed "Sheet 2", which has values that are dependent on "Sheet 1". On Sheet1, I have dropdowns that show/hide rows depending on what is in chosen (see above code). With each option, there are ~100 rows, many of which have a 0 value; can I apply Mat's above code to this, and have it run every time a new thing is chosen from the dropdown so only the rows with a non 0 value are shown?

**AB33** · 02-15-2013, 06:21 PM

Filter code would not take more than 10 seconds to run, but I am not a big fun of filtering. I am sure there are many people who can help you with.

**mat.davies** · 02-15-2013, 06:26 PM

Hi

Trying to remember what I meant myself! It would be complicated, but instead of hiding the range straight away, you could just select some rows (until you have a predefined no, say batches of 10) and then use .hidden=true in groups.

I think you could have a rolling selection string, which starts of as null, and add each row to be hidden, using something like

Please Login or Register  to view this content.

such that you could use it in a rows() call (with the trailing comma removed). this would then let you use

Please Login or Register  to view this content.

to see how many sets you'll be hiding, using

Please Login or Register  to view this content.

would then hide the batch. If you tried to do it all at once, you'll likely hit some kind of limit for the number of areas in a range (although maybe not)

This is a bit of a brain dump, and is a bit muddled. Let me know if you'd like something a bit more complete.

cheers

Mat

**Toonies** · 02-15-2013, 06:31 PM

hi have a look at this

http://www.excelforum.com/excel-prog...hide-rows.html

**Sky188** · 02-28-2013, 04:04 PM

Mat,
I'm unfamiliar with the concepts you're proposing, and have spent the last week or so trying to fully understand it so I can utilize it, and I have unfortunately failed. Are you willing to elaborate a bit?