How to hide and unhide rows from a click in the cell.

[uscayan]

I want to be able to double click on any cell in column ‘A’ and if there is a value in the cell, it checks for blanks below it and hides the following blank rows until it finds another cell that has a value in it. Than if double clicked in the same cell, it unhide the hidden rows. For example if I double click on A1, nothing happens but if I double click on A3, it hides rows 4 to 20 because the next cell in column ‘A’ that has value in it is row 21. Then if I double click A3 again, it unhide it. This same logic applies to column ‘B’ and ‘C” and so on. It can go up to 7 columns deep.



Any help with this would be greatly appreciated.

[sktneer]

Hi,
Welcome to the forum.

Try this............

Right click on Sheet Tab --> View Code --> Paste the code given below in the code window --> Save your workbook as Macro-Enabled Workbook.

Please Login or Register  to view this content.

[uscayan]

sktneer,

It didn't work. Is there anything else I can do.

[Ajohn403]

I just tested the code given with many different combinations of numbers and columns, and it worked great.
Just make sure you're posting it in the Sheet's code, and not as a run-able macro.
After pasting the code save as a Excel Macro-Enabled Workbook
1.png2.png

[uscayan]

That was weird. Now it works great. Sktneer, thanks for writing the code and Ajohn403 thanks for trying it.

[uscayan]

I just thought of another thing to help improve this function. When I double click on a cell and it hides the rows below it. Is it possible to turn that cell red so that it is clear that there as been rows hidden below it. Sorry to tag it along with this.

[sktneer]

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

[sktneer]

Replace the existing code with the following one...

Please Login or Register  to view this content.

[uscayan]

Nice! Very Nice. Thank you sktneer.

[sktneer]

You're welcome.

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.