How to expand and collapse (unhide and hide) rows in a tree like structure

Dear forum

Please see my attached spreadsheet. I have a tree like structure with three tiers of branches that I want end users to be able to collapse and expand by clicking on cells marked “-“ and “+”. I have in mind the code that would work, but I lack the knowledge to draft it. I thought a solution would be to run a SelectionChange procedure that would, if the collapse symbol “-“ is clicked, hide all rows starting from the next row to the row in which I would put a text string, eg “stop” in same column below the collapse ”-“ symbol. That way I could add new rows of data and still have the procedure work. At the moment I have coded a very inflexible series of macros linked to static cells. I am aware of Excel's built in tree structure functionality, but I dont like the look of it, and it isnt quite suited to what I need.

As always any assistance would be greatly appreciated.

Scott N

rendi-tsgsample.xlsI have looked around for some code. The closest I could find was in the attached workbook, which looked for blank cells in a particular column, and then hid the row in which the blank cell was found. Unfortunately, the code is static - it requires the input of the starting row and the end row to work. What I am looking for is something that is dynamic, a procedure that can identify where the last row is to be hid by either me putting in a text string "Stop", or it finds some other text in the cell, such as the next item in the same tier of the tree.

Any help would be greatly appreciated.

Regards

Scott N

The rules are simple for this spreadsheet. Everything in the tree must have a + or - sign. The branches must be 2 columns apart also.

tree.xlsm

Thanks Mike and Yraen.

Your code was greatly appreciated. I love the tree.xlsm and have used it in my project = problem solved.

Thank you so much to both of you for your help.

Regards

Scott

You're welcome. It was fun to create it.