Possible to have a cell detect if there are hidden rows and yield a YES/NO result?
I am designing a worksheet and it will be used by people other than myself. While I will remember if I have hidden rows or not, someone else may not remember to check.
Is there a way to make a cell that can check the sheet for hidden rows, and if so yield just a Yes/No result.
I want to just make a cell that says "Hidden Rows?" and the one next to it say "YES" or "NO".
You could use a UDF.
To add to workbook hit Alt+F11, Insert->Module and paste it there. Close that window.
Please Login or Register to view this content.
Then in let's say A2 put =HiddenCheck(A:A)
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
@daffodil11: if there are NO hidden rows, won't it be a bit slow if you check an entire column?
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Yes, it's terrible, especially considering I made the function volatile. A solid 2-3 seconds.
VB is your more realm than mine. My brain just couldn't come up with something that didn't literally check everything.
Perhaps if we knew that a single Column might always have data in it that's being hidden, we could check the difference of counts? The instrinsic functions always seem to process a great deal more efficiently, such as COUNTBLANK(A:A)
If there are no empty cells within the data range you could use a SUBTOTAL = COUNTA formula. TRUE = no hidden rows. FALSE = hidden rows.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There we go. I'm getting too old for this.
After some quick testing it seems that empty cells would not affect the result.Originally Posted by Tony Valko
Clarification...
If the hidden rows (if any) will only be within the data range try something like this:
A1 = Hidden Rows?
This formula in B1:
=IF(SUBTOTAL(103,A:A)=COUNTA(A:A),"No","Yes")
Sorry for the slow reply back folks.
First of thank you for all the help. Good to see so much feedback.
The rows that are being hidden have some zero values but not all (example, Column A and Column B could have separate zero values causing me to need to hide the row.) However I could probably use an OR statement in there to get it to work. However I will probably use the Module you made instead. It may take a few seconds, but that is fine.
Thank you folks!
Good deal. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks