How to maintain a Filter without resetting when Grouping and Ungrouping rows

[Grimm127]

Hi All,

I am running a filter with a bunch of groups for my PnL.
The challenge is that I have a column that lets me know that a particular row has no values and to reflect "H" (Hide) or "K" (Keep). So when I use the filter it hides the empty rows for that particular PnL. Its a template so these fields will change as I switch to different accounts.

The problem is that I also have groups in the PnL. So as I open and close the groupings the filter is override so it is showing everything again.

Is there a VBA code I can use that when ever I open or close a group it automatically reapplies the filter?

Thank you for your time in advance.......

 

[k3itall]

Hi,

for all of you who encounter this problem nowadays...
Grouping / Filtering / Hiding Columns and Rows are using the the same core function in Excel.
So if you collapse a group, then Excel basically is hiding the grouped rows.

-> so maintaining the filtering does not work.


My solution was to

• remove the excel grouping
• add a column with "x" which shows which row should be displayed
• create a "+"button at each later group headline
• make a short VBA makro to hide / show everything which does not have an "x"

Its actually just as fast as the Excel grouping.
Sadly its really the only way, I could imagine.

maybe also look at this
Grouping and ungrouping rows in excel using VBA assigning x1 button for opening and closing