Hello everybody
I have this function that counts the hidden cells in a range
I want to update results after hiding and unhiding
I tried Application.volatile but I had to double click in any cell to update results !!
Please Login or Register to view this content.
Changing row height or column width does not force a recalc. But you need to force a recalc, so you can do that manually using Ctrl-Shift-F9, or by using the sheet's selection change event.
Bernie Deitrick
Excel MVP 2000-2010
Can we add any line withing the udf funtion that force recalc?
No. You could time a macro to run every x minutes that would force a re-calc, but UDFs are not able to run other code - they are run by either macros, other functions, or spreadsheet cells.
Well Yasser,I want to update results after hiding and unhiding
its working for me. OR may be it didn't understand your question.
Try posting a workbook...
check the attached file:-
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Mr. Vikas thanks for sharing
As for hiding rows everything is ok .. Try hide column B
Okay here is one possible workaround.
Copy the following code and paste it in the ThisWorkbook Module:-
Paste following code besides your Function or in a new module.HTML Code:
Try hiding column B and observe the change.HTML Code:
Check the attached file:-
Thanks Mr. Vikas for this try
Try again to unhide the Column B after hiding it !!!
Another point I think using Recalling for continuous checking will make the file slow somewhat or Am I not right about this point?
Finally Done it...
Here is the final Code:-
Please Login or Register to view this content.
Great Mr. Vikas
But I wonder Recalling for continuous checking will make the file slow or not?
Is it acceptable in point of most excel users' view?
Oddly enough, adding a dummy Trigger argument and passing it a whole column reference triggered recalc when rows were hidden (but not when columns were hidden)
=VisibleCount(B2:D4, A:A) recalculated when rows were hidden or un-hidden or when filters were applied, but it didn't recalculate when columns were hidden.Please Login or Register to view this content.
Changing to a whole row Trigger, as in = VisibleCount(B2:D4, 1:1) didn't change the behavior
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
I trust you will find a way to skip this little trouble to get the perfect UDF for this ..but not when columns were hidden
Another point I want to count the hidden cells not the visible ones
Please Login or Register to view this content.
Excellent Mr. mikerickson
Really perfect approach..
Just the last little trouble and everything will be ultimate perfect
It isn't going to happen. Excel doesn't trigger calculation from hiding columns the same way that it does with hiding rows. (SUBTOTAL doesn't work that way).
Either you'll have to redesign your worksheet so that a continuous count of hidden cells is unnecessary (why does the user (or a downstream formula) care how many cells are visible?)
or so that hiding columns doesn't happen.
Or you'll have to go with something like Vilas solution that is resource heavy. Fighting Excel to make it do something it isn't designed for is going to be resource intensive.
Last edited by mikerickson; 02-22-2015 at 11:33 AM.
Here are some performance improvements.Originally Posted by YasserKhalil
1. Copy following codes in the New Module.
New CountHiddenCells Code:-
Recalc:-HTML Code:
2. Paste following code in the ThisWorkbook Module:-HTML Code:
Check the attached file:-HTML Code:
Thanks Mr. Vikas_Gautam for challenging that but it seems to be impossible to avoid that. As the same thing with hiding column and unhiding it. Generally thanks a lot for your great tries
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks