How can I trigger a macro by grouping or ungrouping rows

[declan harte]

Hi,

I have a macro that starts when I select a product in cell b1. It hides any line of my income statement that has a zero (does not have any value for this month)

This is a very long and detailed income statement so the accounts are grouped into categories so I would also like the hide rows macro to be initiated when someone groups or ungroups the data.

Any help would be appreciated.

Thanks,

Declan

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$1" Then Hide_Rows
End Sub

Sub Hide_Rows()
Dim i As Long
Application.EnableEvents = False
For i = 11 To 750
  Rows(i).Hidden = Range("B" & i).Value = 0
Next i
Application.EnableEvents = True
End Sub

 

[vletm]

declan harte
hide rows macro to be initiated when someone groups or ungroups the data.
Means ... if someone ungroups then hiding ... hmm?

Could You use Application.ScreenUpdating?
(and WorkSheet_SelectionChange)
It took 1sec to hide those rows (instead 20secs).
Copy that to Your sheets code-page.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Application.ScreenUpdating = False
        With ActiveSheet
            For y = 11 To 750
                If .Cells(y, 2) = 0 Then .Cells(y, 2).RowHeight = 0
            Next y
        End With
        Application.ScreenUpdating = True
    End If
End Sub

 

[declan harte]

Hi vletm,

The macro doesn't start when I ungroup or group the rows. Also, when I click on B1 to select the product, the macro starts before I can make my selection.

Thanks,

Declan

 

[Hui]

Why not add two buttons

Button 1 - Hide all rows with zeroes in current Month
Button 2 - UnHide all rows with zeroes in current Month

That way the groupings won't impact display of Zeroes

 

[declan harte]

Hi Hui,

I don't think I've described my issue correctly.

The user of the file can either select a product in b1 and / or ungroup or group the pre grouped data to see more or less account detail. At all times, any row that has a zero in Column B should be hidden.

Sorry for the confusion,

Declan

 

[NARAYANK991]

Hi ,

If the grouping / ungrouping will only be done after a product selection in cell B1 has been done , then why do you need the macro to run each time a user does grouping / ungrouping ?

Narayan

 

[Hui]

But if you hide all rows with a zero in the current month, it doesn't matter how you sort, group/ungroup or what level of detail you look at, as they will be hidden already

 

[vletm]

declan harte
As I wrote:
hide rows macro to be initiated when someone groups or ungroups the data.
Means ... if someone ungroups then hiding ... hmm?
That won't work!
Cell B1 is like a trigger/button - There cannot be nothing to select!
Isn't You one challenge that it takes too long time?
This or any depends of You sheet ... which (or sample) You haven't upload.

 

[declan harte]

Here is the file.

Any accounts in a category with a zero value should be hidden whether I group or ungroup that account category. If there are 20 accounts in a category and 10 have no values, when I ungroup the category, the 10 accounts should still remain hidden.

The grouping/ungrouping doesn't have to be done after I select the product.
The user can decide to just look at the existing product selected, he can choose to ungroup or ungroup categories. He is in control. I just want to make sure no matter what he does, no rows with zero values will be seen whether that's on a level 1 view or a level 5 view.

I hope this helps a bit more.

Thanks for your patience.

Declan

 

[vletm]

declan harte
- Any help would be appreciated.
- I hope this helps a bit more.
Question: Are You sure that You need those formulas?
I would try to do something after 'clean' those 'away'.
Before that, I can only wish good luck.

 

[vletm]

declan harte
I had some time to test:
There were 'nice font' in C-column ... but no matter for me.
I tried to figure Your formulas ... and after that I deleted all of those.
... anyway, now this is much quicker ... no idea of results!
'Is it that if sum or row of columns from D to Q is zero then hide it?'
Still, no functionality to hide rows while 'grouping'!

Question: Why there is 'Summary'-sheet?
You could do same with 'Detail'-sheet ... add those 'Variances'.

 

[declan harte]

The detail sheet is the original P and L that came from another dept. It has 700 accounts and the data is shown over 200 columns. It's not user friendly so I was tasked to clean it up hence the summary tab.

The summary tab.

• Uses Sumproduct formulas to lookup up the relevant data on the detail for the product selected, the user now only sees 20 columns instead of 200.

• Adds variance formulas that weren't built into the detailed tab so the user can zero in on the forecast misses.

My hide rows macro was a further attempt to clean up things by hiding the rows with no Ytd activity.

If it's not possible to trigger the macro when grouping or ungrouping the categories, I may just have to delete those rows instead.

Thanks for your input,

Declan

 

[vletm]

declan harte
Did You offer 'a Sample File' which is different than
Your 'original file' which You should use?
If so then ... why? It's challenge to 'help' with misinformation!
> Upload Correct Sample File >
If it's same or it has same kind of layout
... clear patterns
... then NO NEED those 'massive formulas'!
... neither NO need double formulas!
and
it's possible to do it as in my sample
or even without 'Summary'

> Did You compare my sample to Your 'file'?

My opinion is that 'deleting' won't solve Your challenge!

 

[declan harte]

Vletm

I have been patient with your rude responses to my question about to now but if this is the kind of disrespect that is tolerated on this forum, I will ask my questions elsewhere.

 

[vletm]

declan harte
I don't give r..e responses, I know rules and I'm following those.
I tried to give solutions for You, but if those won't match, I cannot help.
Please, let me know response after You have gotten that.