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]

columns or rows ???
Isn't it better to use a slicer or something else to exclude some items, otherwise your (sub)totals aren't correct.

 

[BSALV]

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
          With Worksheets("CST View").PivotTables("AutoOL")
               .PivotCache.Refresh
               MsgBox .TableRange1.Address                      '--> just temporary, so that you can see where that range is
               For Each c In .TableRange1.Columns(3).Cells      '--> all the cells in the 3rd column of that range
                    c.EntireRow.Hidden = IIf(c.Value = "x", True, False)     'hide the cells with a "x
               Next
          End With
     End If

End Sub

 

[Binbs]

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
          With Worksheets("CST View").PivotTables("AutoOL")
               .PivotCache.Refresh
               MsgBox .TableRange1.Address                      '--> just temporary, so that you can see where that range is
               For Each c In .TableRange1.Columns(3).Cells      '--> all the cells in the 3rd column of that range
                    c.EntireRow.Hidden = IIf(c.Value = "x", True, False)     'hide the cells with a "x
               Next
          End With
     End If

End Sub

Hi BSALV,

Thanks for your response. I do need to hide rows, not columns and subtotals are not a concern.

This doesn't appear to work as the rows with an "x" are not hidden after the loop runs.

In my data source, I have vba that is refreshing the pivot as soon as an "x" is placed in a row cell in my "Exclude" column.

The hope is that as soon as this is done, that the corresponding row will be hidden in the pivot since those are items the user should not see.

I don't need for a check to be run each time the cells in Range("I7:J7") are updated since that is intended to refresh a different set of variables. It's better not to run the loop when I7:J7 are updated since those cells will be updated often by users and a loop will be slow.

Also, I'm not sure why, but each time the loop runs, the prompt below appears.

Appreciate your help

 

[Binbs]

columns or rows ???
Isn't it better to use a slicer or something else to exclude some items, otherwise your (sub)totals aren't correct.

Definitely thought of this. The pivot I am using is a list of items for sale. The concern is that a user may inadvertently un-filter an item meant to be excluded.

 

[Binbs]

Hi BSALV,

Thanks for your response. I do need to hide rows, not columns and subtotals are not a concern.

This doesn't appear to work as the rows with an "x" are not hidden after the loop runs.

In my data source, I have vba that is refreshing the pivot as soon as an "x" is placed in a row cell in my "Exclude" column.

The hope is that as soon as this is done, that the corresponding row will be hidden in the pivot since those are items the user should not see.

I don't need for a check to be run each time the cells in Range("I7:J7") are updated since that is intended to refresh a different set of variables. It's better not to run the loop when I7:J7 are updated since those cells will be updated often by users and a loop will be slow.

Also, I'm not sure why, but each time the loop runs, the prompt below appears.

View attachment 54817

Appreciate your help

Please disregard my message regarding the message prompt. I understand now that was temporary so that I could see the ranges.

 

[Binbs]

Okay! Now I realize the vba to hide rows in my pivot should be in my data source sheet, not the pivot sheet. This is still not hiding the rows with an "x" but I think it is really close to working. I think where I am stuck is which column to use in this line of code For Each c In .TableRange1.Columns(3).Cells

"Exclude" is the 14th column in the pivot and the 43rd column in the data source. I've tried both but it still is not working.


Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Set KeyCells = Range("AQ:AQ")

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

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


With Worksheets("CST View").PivotTables("AutoOL")
.PivotCache.Refresh
MsgBox .TableRange1.Address '--> just temporary, so that you can see where that range is
For Each c In .TableRange1.Columns(43).Cells '--> would this be the column in the pivot or data source?
c.EntireRow.Hidden = IIf(c.Value = "x", True, False) 'hide the cells with a "x
Next
End With
End If
End Sub

 

[BSALV]

misunderstood, the x's aren't in the pivottable, is it?
Your KeyCells are now cells in the 43st column = AQ, not in I:J
But how do you unhide them ?

Sub Worksheet_Change(ByVal Target As Range)

     Dim KeyCells As Range

     Set KeyCells = Range("AQ7:AQ27") 'cells in the 43rd column

     Set c = Intersect(KeyCells, Target) 'only the changed cells in KeyCells
     If Not c Is Nothing Then
          For Each c0 In c.Cells                                '--> all the changed cells in KeyCells
               c0.EntireRow.Hidden = IIf(c0.Value = "x", True, False)     'hide the cells with a "x
          Next
     End With
End If

End Sub

 

[Binbs]

misunderstood, the x's aren't in the pivottable, is it?
Your KeyCells are now cells in the 43st column = AQ, not in I:J
But how do you unhide them ?

Sub Worksheet_Change(ByVal Target As Range)

     Dim KeyCells As Range

     Set KeyCells = Range("AQ7:AQ27") 'cells in the 43rd column

     Set c = Intersect(KeyCells, Target) 'only the changed cells in KeyCells
     If Not c Is Nothing Then
          For Each c0 In c.Cells                                '--> all the changed cells in KeyCells
               c0.EntireRow.Hidden = IIf(c0.Value = "x", True, False)     'hide the cells with a "x
          Next
     End With
End If

End Sub

Thanks again BSALV. Starting over since that is not working.

Here is the original vba code in my data source. This will refresh my pivot whenever an "x" in entered into a row cell (or any changes at all are made to the data source).

Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Set KeyCells = Range("A:AQ")

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

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

End If

End Sub

Data source

I need this code to refresh the pivot whenever the data source changes but also hide any rows in the pivot with an "x"

Pivot

If an "x" is removed in the data source, it should also unhide that item in the pivot.

Is this possible?

 

[Binbs]

Thanks again BSALV. Starting over since that is not working.

Here is the original vba code in my data source. This will refresh my pivot whenever an "x" in entered into a row cell (or any changes at all are made to the data source).

Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Set KeyCells = Range("A:AQ")

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

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

End If

End Sub

Data source
View attachment 54828

I need this code to refresh the pivot whenever the data source changes but also hide any rows in the pivot with an "x"

Pivot
View attachment 54830

If an "x" is removed in the data source, it should also unhide that item in the pivot.

Is this possible?

Thanks again BSALV. Starting over since that is not working.

Here is the original vba code in my data source. This will refresh my pivot whenever an "x" in entered into a row cell (or any changes at all are made to the data source).

Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Set KeyCells = Range("A:AQ")

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

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

End If

End Sub

Data source
View attachment 54828

I need this code to refresh the pivot whenever the data source changes but also hide any rows in the pivot with an "x"

Pivot
View attachment 54830

If an "x" is removed in the data source, it should also unhide that item in the pivot.

Is this possible?

I should also say that the code would hopefully not run the loop to check for changes to the "Exclude" column if anything other than that specific column is updated in the data source.