Ungroup Rows On Cell Click?

  • Is it possible to ungroup all rows using a cell click instead of the default option that excel gives?

    I know this seems a bit pointless but i submit my excel spreadsheets to many people that do not notice the plus & minus signs that excel creates & misses the hidden data.

    This way would allow me to put a simple Open / Close text in a cell that would be obvious to click on.

    Any help appreciated.


  • Re: Ungroup Rows On Cell Click?

    Hi financial host,

    Using the Worksheet_SelectionChange is one way you can do it.

    In the Code, I have used cell E1 as the cell to click. You will have to change this to suit. You will see that it also Activates cell B12, which is a cell within my data range for the Subtotals, again you may have to change this.

    When the user clicks to Add subtotals, the Text in E1 changes to Click to Remove Subtotals and vice versa.


  • Re: Ungroup Rows On Cell Click?


    I could not get this to work properly to do this...

    Probably is now i can't seem to delete the script properly.

    Does anyone know how to delete this script & reset the spreadsheet?

  • Re: Ungroup Rows On Cell Click?

    Hi Financial Host,

    the line:

    Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(12), _

    in the code contains the headers in the Array, so if your data only has 5 columns, change the Array(12) to Array(5) or however many columns you have.

    There is no other reason why it should not work.


  • Re: Ungroup Rows On Cell Click?

    I am sorry - i just dont get it...

    I get runtime error 101

    At this point, i would just like to remove the code but don't seem able to.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!