search column and select rows

**robotlust** · 07-20-2008, 03:30 AM

hi,
I have a very large spreadsheet i'm running a script to hide rows given the value of a cell in a column.
Given its size the operation takes about 15-20 minutes to execute.

Please Login or Register  to view this content.

I think its taking too long because it's selecting the row, then hiding, and loops through until finished.

My Question: Would it be quicker to have a Sub run through making non-adjacent selections of the rows that qualify then do 1 Hide Row operation? And what would be the best way to do this? Have done some searching but cant find a way.

Cheers
Cam

**se1429** · 07-20-2008, 04:00 AM

Hi,

Or may be you can sort your data by column 45, make the hide in one shot and then sort your data back. Of course the feasibility of this option depends on the structure of your data.

Regards.

**robotlust** · 07-20-2008, 04:05 AM

Hi,
Afraid I cant do that. it's several reports in one where the Sub hides rows that have not returned a value from an array.

**VBA Noob** · 07-20-2008, 04:36 AM

You could try adding the below.
It turns off the screenflicker and also if your dealing with formulas it may help speed things up

Please Login or Register  to view this content.

'Your Code

Please Login or Register  to view this content.

VBA Noob

**se1429** · 07-20-2008, 04:42 AM

Ok, try this:

1. Do autofilter for your data.
2. In column 45 click the arrow of the list box.
3. Select custom.
4. In the list where it says "show rows where" .. select "Does not equal".
5. In the list to the right select 1.

This will hide all the rows with value "1".

I hope it will satisfy your request.

Regards.