VBA to automatically hide rows in pivot based on column

[Binbs]

Hello,

I would like to automatically hide a row in my pivot table when a cell in column "Exclude" (column O) is equal to "x".

I currently have the code below which is refreshing my pivot when cells in I7:J7 are updated and it's working beautifully. When I try to include code in bold below to hide rows in column O when cells are equal to "x", it does not work.

Any help is very appreciated.

Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Set KeyCells = Range("I7:J7")

If Not Application.Intersect(KeyCells, Target) Is Nothing Then

Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh

ElseIf Range("O:O") = "x" Then
Rows("O:O").EntireRow.Hidden = True

End If
End If
End Sub

Thank you

 

[BSALV]

if you reorganize your pivottable and you place your exclusive as 1st field in the rowrange and unselect it, you only have to refresh the table and everything is ok. I think you make it too complicated.
I'll make an example ....

left table with data and a column to make rows exclusive.
2 pivottables, the 1st with exclusive in the filterarea, left top, and i filtered so that the "x" isn't shown.
The 2nd has exclusive as first item and you have a x-part and an empty part.
piece of a cake, which fits best ?

Map1
	A	B	C	D	E	F	G	H	I
1	item	qty	exclusive				exclusive	(leeg)
2	a	5	x
3	b	4					Rijlabels	Som van qty
4	c	3					c	3
5	d	2					e	1
6	e	1					b	4
7							d	2
8							Eindtotaal	10
9
10
11
12							Rijlabels	Som van qty
13							x	5
14							a	5
15
16							(leeg)	10
17							c	3
18							e	1
19							b	4
20							d	2
21
22							Eindtotaal	15
23
Blad1

 

[Binbs]

if you reorganize your pivottable and you place your exclusive as 1st field in the rowrange and unselect it, you only have to refresh the table and everything is ok. I think you make it too complicated.
I'll make an example ....

left table with data and a column to make rows exclusive.
2 pivottables, the 1st with exclusive in the filterarea, left top, and i filtered so that the "x" isn't shown.
The 2nd has exclusive as first item and you have a x-part and an empty part.
piece of a cake, which fits best ?


[I wish it were this simple. If I understand this solution, it would be possible for a user to mistakenly "Clear filters" and view items marked with an "x" which would be problematic.

 

[BSALV]

Private Sub Worksheet_Change(ByVal Target As Range)
   
     Set c0 = Intersect(Target, Range("F:F"))                   'intersect target with column F
     Set c1 = Intersect(Target, Range("AQ:AQ"))                 'intersect target with column AQ

     If Not c0 Is Nothing Or Not c1 Is Nothing Then             'something changed in 1 of these columns
          With Worksheets("CST View")                           'this worksheet
               Set pvt = .PivotTables("AutoOL")                 'this pivottable
               pvt.PivotCache.Refresh                           'refresh
               Set c2 = Intersect(pvt.RowRange, .Columns("O"))  'intersect of column O and the PVT rowrange
               For Each c In c2.Cells                           'loop through all those cells in the pivottable
                    c2.EntireRow.Hidden = (c2.Value <> "")      'hide or unhide row if cell is empty or not
               Next
          End With
     End If
End Sub

 

[Binbs]

Private Sub Worksheet_Change(ByVal Target As Range)
  
     Set c0 = Intersect(Target, Range("F:F"))                   'intersect target with column F
     Set c1 = Intersect(Target, Range("AQ:AQ"))                 'intersect target with column AQ

     If Not c0 Is Nothing Or Not c1 Is Nothing Then             'something changed in 1 of these columns
          With Worksheets("CST View")                           'this worksheet
               Set pvt = .PivotTables("AutoOL")                 'this pivottable
               pvt.PivotCache.Refresh                           'refresh
               Set c2 = Intersect(pvt.RowRange, .Columns("O"))  'intersect of column O and the PVT rowrange
               For Each c In c2.Cells                           'loop through all those cells in the pivottable
                    c2.EntireRow.Hidden = (c2.Value <> "")      'hide or unhide row if cell is empty or not
               Next
          End With
     End If
End Sub

This looks good! Do you know how I can get around this type mismatch error that is popping up when I run the code? Is it happening with this line:

Thanks very much for your help.

 

[BSALV]

with a msgbox, you see the address of c2 (is for tempory use of course)
If you check that range, is there something special (error, ...) in 1 of those cells ?
With len(c2.value) you check now the length of the values in the cells

Set c2 = Intersect(pvt.RowRange, .Columns("O"))                 'intersect of column O and the PVT rowrange
MsgBox c2.Address
For Each c In c2.Cells                                          'loop through all those cells in the pivottable
     c2.EntireRow.Hidden = (Len(c2.Value) > 0)                   'hide or unhide row if cell is empty or not
Next

 

[Binbs]

with a msgbox, you see the address of c2 (is for tempory use of course)
If you check that range, is there something special (error, ...) in 1 of those cells ?
With len(c2.value) you check now the length of the values in the cells

Set c2 = Intersect(pvt.RowRange, .Columns("O"))                 'intersect of column O and the PVT rowrange
MsgBox c2.Address
For Each c In c2.Cells                                          'loop through all those cells in the pivottable
     c2.EntireRow.Hidden = (Len(c2.Value) > 0)                   'hide or unhide row if cell is empty or not
Next

With the msgbox, I see the address is $O$20:$O$794. That looks correct. Then I get the type mismatch error again.

I checked the range and there is nothing that looks unusual to me such as an error. Just (blank) or x.

 

[BSALV]

very stupid error, c2 instead of c

For Each c In c2.Cells                                          'loop through all those cells in the pivottable
     c.EntireRow.Hidden = (Len(c.Value) > 0)                   'hide or unhide row if cell is empty or not
Next

 

[Binbs]

very stupid error, c2 instead of c

For Each c In c2.Cells                                          'loop through all those cells in the pivottable
     c.EntireRow.Hidden = (Len(c.Value) > 0)                   'hide or unhide row if cell is empty or not
Next

Thanks so much for all your help!

 

[Binbs]

I found that the last code would hide all columns, not just those listed with an "x" for some reason.

I changed the value to -1 instead of x and used this:

For Each c In c2.Cells                                          'loop through all those cells in the pivottable
     c.EntireRow.Hidden = (c.Value) < 0                         'hide or unhide row if cell is empty or not
Next

This works great and hides only the rows I need to hide. However, anytime I filter or use a slicer, the hidden rows are unhidden. When I remove the filter or slicer, they are hidden again. Any idea why this happens?

Thank you

 

[BSALV]

excel tries to be smarter, to be helpfull and most of the time it's oké, but here not.
you have to correct it with the Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) - event.
You have to write exact the same as in the other event, or make 1 macro and call him from each event.