Put a dynamic hint for showing or hiding rows or columns hidden by Data Group - EBOOK VBA EXCEL

EBOOK VBA EXCEL

EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Search This

Tuesday, December 3, 2019

Put a dynamic hint for showing or hiding rows or columns hidden by Data Group

Put a dynamic hint for showing or hiding rows or columns hidden by Data Group

This is about using SUBTOTAL and CELL function to detect whether a row and a column is hidden respectively.
When rows are hidden by Data–>Group, you will see a +/- button on the leftmost of  spreadsheet to remind you that you may expand or collapse the data you are looking at.Excel Tips - Dynamic Hint for showing or hiding hidden rows

To me, this +/- button is obvious enough.  However, my experience told me that it is NOT.  Some users simply have no idea what is the button for.  And for some reasons I cannot understand, they do not even try to click on it to see what will happen.
That gives me a thought of putting a hint on the cell next to it to remind user.  Like the screenshot below:
Excel Tips - Dynamic Hint for showing or hiding hidden rows 2
Yes.  This can be done by using SUBTOTAL function, together with a simple IF statement
In our example, input the following in C17:
=IF(SUBTOTAL(103,A16)=0,"<== Click the |+| to expand", "<== Click the |-| to collapse")
The SUBTOTAL 103 performs a COUNTA function to the range A16.  If A16 is visible, SUBTOTAL counts it; if A16 is hidden, SUBTOTAL ignores it. (This is what the function number 103 designed for).  In other words, SUBTOTAL(103,A16) gives a result of either 1 (if A16 is visible) or 0 (if A16 is hidden), provided that A16 is a non-blank cell.

Then the rest of =0 and the IF statement should not be difficult to understand.  It means: “IF A16 is hidden, then “<== Click the |+| to expand”, else <== Click the |-| to collapse”

What about hidden columns?  SUBTOTAL doesn’t work for columns…

Excel Tips - Dynamic Hint for showing or hiding hidden columns
Yes. You are right.  SUBTOTAL is designed for visible rows, not columns.
We need CELL function to help.
In our example, input the following in L1:
=IF(CELL("width",K1)=0,"↑ Click the |+| above to expand", "↑ Click the |-| above to collapse")
Thanks CELL function for making the trick possible.
The syntax:
=CELL("info type",[Ref])
“Width” will give you the column width of the reference cell rounded off to an integer.  By definition, the column width of a hidden column is 0 (zero).   That makes sense,  right?
Now we know that CELL(“width”,K1)=0 means column K is hidden.  Then the formula returns “↑ Click the |+| above to expand”“↑ Click the |-| above to collapse” depending whether column K is hidden or not.
You may download a Sample File HERE.
With this little trick, we may find a way to SUM visible columns only – a situation when SUBTOTAL does not work.  Let’s talk about it next week.  Stay tuned.

Just in case you do not know how to GROUP Data…
Select the rows (or columns) you want to group, then go to Data tab –> Outline group –> Group
Excel Tips - Dynamic Hint for showing or hiding hidden rows 3
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment