I can't filter specific values in VBA code

[DrBacon]

Hello all,

I am trying to run a code that filters out only the values in certain cells. But when I try to run the code, I keep on getting " Unable to get the pivotfields property of the PivotTable class. Here is the code:

Sub FilterPivotItems()

Dim PT          As PivotTable
Dim PTItm      As PivotItem
Dim FiterArr()  As Variant
Dim first As String
Dim second As String
Dim third As String
Dim fourth As String


first = Range("a26").Value
second = Range("a27").Value
third = Range("a28").Value
fourth = Range("a29").Value


' use an array to select the items in the pivot filter you want to keep visible
FiterArr = Array(first, second, third, fourth)


' set the Pivot Table
Set PT = Sheets("Lowest Scores").PivotTables("PivotTable4")


' loop through all Pivot Items in "Value" Pivot field


For Each PTItm In PT.PivotFields("Value").PivotItems


    If Not IsError(Application.Match(PTItm.Caption, FiterArr, 0)) Then ' check if current item is not in the filter array
        PTItm.Visible = True
    Else
        PTItm.Visible = False
    End If
    
Next PTItm


End Sub

What am I doing wrong?

 

[DanteAmor]

If no element meets the condition, then the macro tries to hide all, but it is not possible to hide all, so it sends you the error.

could you put
On error resume next
Before the for


​

 

[DanteAmor]

you can complement it like this

Sub FilterPivotItems()


Dim PT          As PivotTable
Dim PTItm      As PivotItem
Dim FiterArr()  As Variant
Dim first As String
Dim second As String
Dim third As String
Dim fourth As String




first = Range("a26").Value
second = Range("a27").Value
third = Range("a28").Value
fourth = Range("a29").Value




' use an array to select the items in the pivot filter you want to keep visible
FiterArr = Array(first, second, third, fourth)




' set the Pivot Table
Set PT = Sheets("Lowest Scores").PivotTables("PivotTable4")




' loop through all Pivot Items in "Value" Pivot field


[COLOR=#0000ff]On Error Resume Next[/COLOR]
[COLOR=#0000ff]Dim wVisible As Boolean[/COLOR]
[COLOR=#0000ff]wVisible = False[/COLOR]
For Each PTItm In PT.PivotFields("Value").PivotItems




    If Not IsError(Application.Match(PTItm.Caption, FiterArr, 0)) Then ' check if current item is not in the filter array
        PTItm.Visible = True
[COLOR=#0000ff]        wVisible = True[/COLOR]
    Else
        PTItm.Visible = False
        
    End If
    
Next PTItm


[COLOR=#0000ff]If wVisible = False Then[/COLOR]
[COLOR=#0000ff]    MsgBox "No matches"[/COLOR]
[COLOR=#0000ff]end if[/COLOR]
End Sub

 

[DrBacon]

You rock man, thanks. But the problem now is that I might not be using the correct code. The values do exist, but they are not filtered. Lets say that the 4 values in the array are red, blue, green and yellow. The pivot table does show the values, but nothing is getting filtered.

How could I go about doing this?

 

[DanteAmor]

I would have to review your data to see why it is not filtered.
You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[DrBacon]

For security, we are not allowed to upload files, but here is what I have been trying, but I keep on getting errors:

Sub FilterMyPivot()

     Sub FilterMyPivot()

    Dim first As String
    Dim second As String
    Dim third As String
    Dim fourth As String
    Dim myPivot As PivotTable
    Dim pItem As PivotItems
    
    Application.ScreenUpdating = False
    
    first = Range("a26").Value
    second = Range("a27").Value
    third = Range("a28").Value
    fourth = Range("a29").Value


    
    Worksheets("Lowest Scores").Activate


Set myPivot = ActiveSheet.PivotTables("PivotTable4")
    
    With myPivot.PivotFields("Nombre Conjunto")
        For Each pItem In .PivotItems
            If pItem.Name <> first Or second Or third Or fourth Then
                pItem.Visible = False
            End If
        Next pItem


    End With
    


End Sub

 

[DanteAmor]

I tried the macro and it works for me.
" If the workbook contains confidential information, you could replace it with generic data."

 

[DrBacon]

I did the following and it did work, no idea why

first = Range("a6").Valuesecond = Range("a7").Value
third = Range("a8").Value
fourth = Range("a9").Value


Set pt = Worksheets("Lowest Scores").PivotTables("PivotTable4")


    With pt.PivotFields("Nombre conjunto")
        For Each pi In pt.PivotFields("Nombre conjunto").PivotItems
        
        
            If pi.Name = first Or pi.Name = second Or pi.Name = third Or pi.Name = fourth Then
                pi.Visible = True
            Else
                pi.Visible = False
            End If
        Next pi
    End With

 

[DanteAmor]

This line, evaluates if pitem.Name is different from "first", but this: OR second, only evaluates if second is true.

[COLOR=#333333]If pItem.Name <> first Or second Or third Or fourth Then[/COLOR]

In this line, evaluate if pi.name is equal to first, or if pi.name is equal to second or if pi.name is equal to third, etc.

If pi.Name = first O pi.Name = second O pi.Name = third O pi.Name = fourth Then

that is the difference