I can't filter specific values in VBA code

DrBacon

New Member
Joined
Sep 4, 2018
Messages
24
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:


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?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
you can complement it like this

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


[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

New Member
Joined
Sep 4, 2018
Messages
24
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Sep 4, 2018
Messages
24
For security, we are not allowed to upload files, but here is what I have been trying, but I keep on getting errors:

Code:
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

DrBacon

New Member
Joined
Sep 4, 2018
Messages
24
I did the following and it did work, no idea why

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
This line, evaluates if pitem.Name is different from "first", but this: OR second, only evaluates if second is true.

Code:
[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.


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

that is the difference
 

Watch MrExcel Video

Forum statistics

Threads
1,108,791
Messages
5,524,897
Members
409,610
Latest member
db321

This Week's Hot Topics

Top