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.
Thanks
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.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$E$1" Then
If Range("IV1").Value = 1 Then
Range("IV1").Value = 0
Range("E1").Value = "Click to Add Subtotals"
Else
Range("IV1").Value = 1
Range("E1").Value = "Click to Remove Subtotals"
End If
Range("B12").Activate
If Range("IV1").Value = 0 Then
Selection.RemoveSubtotal
Else
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End If
End If
End SubWhen the user clicks to Add subtotals, the Text in E1 changes to Click to Remove Subtotals and vice versa.
Bill
Re: Ungroup Rows On Cell Click?
Bill,
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:
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.
Bill
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.
Don’t have an account yet? Register yourself now and be a part of our community!