Loop through rows to see if they are hidden or not and change

**ndjustin20** · 09-16-2011, 04:03 PM

Hello Everyone,

I am looking for a way to loop through a set of rows...row 9 through row 500 and be able to tell if it's property is set to hide and if it is set it to unhide. Any help is much appreciated.

Justin

**tlafferty** · 09-16-2011, 04:08 PM

Please Login or Register  to view this content.

**tlafferty** · 09-16-2011, 04:12 PM

Oops - you just want to see whether or not they are hidden? How would you like this reported? This will give you a count of each type:

Please Login or Register  to view this content.

**ndjustin20** · 09-16-2011, 04:32 PM

Ok soooooooooooooooo unhiding all of them takes FOREVER lol sorry had to yell a bit today it's friday :-) Is there a way to check quickly then only unhide if they are hidden?

**tlafferty** · 09-16-2011, 04:37 PM

I'm betting the use of the variant variable is the culprit. Following uses a collection. See if this is faster:

Please Login or Register  to view this content.

**ndjustin20** · 09-16-2011, 04:43 PM

Hmmmmmmmmmmm still put put put slowwwww :-) So the fastest way seems to be just to hide all then show all rows. I might just have to stick with two buttons until 2010's bug is fixed. The probilem I have is excel 2010 has a bug that when you remove the filters in totality on the whole sheet it takes your text boxes and stacks them on top of eachother in A1....I know....great right lol....so the only way to get around this is to hide all the rows then unhide them from what I can figure out. So I think I might just need to hide all then unhide I'm thinking as that is super fast 8-)

**ndjustin20** · 09-16-2011, 04:46 PM

omg..........you can just use

[CODE]
Rows("9:500").Hidden = True
Rows("9:500").Hidden = False
[\CODE]

I can't believe I never thought of just stacking the two lines of code...AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Thank you so much for your assistance!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! If you are ever in Grand Forks, ND shoot me a message and we'll have a beer or three :-)

justin

**tlafferty** · 09-16-2011, 04:55 PM

Hey - I'd settle for a good cup of coffee...after a long day of looking at code, you can get rummy and miss things you'd ordinarily see otherwise. Glad to help!

Cheers!
Tom