I don't know if my question is really clear, so I'll develop here:
I have a pivot table, and I would like to filter out every row which has no value equal or above 1.
Example:
| label | value 1 | value 2 |
|---|---|---|
| a | 0 | 0 |
| b | 0 | 1 |
| c | 1 | 1 |
| d | 1 | 0 |
| e | 0 | 0 |
I would like to be able to filter it to this :
| label | value 1 | value 2 |
|---|---|---|
| b | 0 | 1 |
| c | 1 | 1 |
| d | 1 | 0 |
is it possible with Excel? I searched everywhere and I couldn't find anything usable with a pivot table...
To do this, we can iterate over PivotItems from RowFields and check the corresponding DataRange for compliance with the stated requirement. For those not suitable, set Visible = False
To filter rows with no values equal to or greater than 1 we can use this code:
Sub Macro_FilterPivot()
Dim pvt As PivotTable
Dim item As PivotItem
With ActiveSheet
If .PivotTables.Count = 0 Then Exit Sub
Set pvt = .PivotTables(1)
For Each item In pvt.RowFields(1).PivotItems
item.Visible = .Evaluate("OR(" & item.DataRange.Address & ">=1)")
Next item
End With
End Sub