problem using VBA filter in Pivot tables

[wassita]

Hello!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am using excel 2007 and I have problems to apply a filter to a pivotfield. I am trying the following:



<o></o>
<o></o>
ActiveSheet.PivotTables(1).PivotFields("Branch").PivotFilters.Add Type:=xlValueEquals, Value1:=2


<o></o>
<o></o>
It generates a run time error '1004' saying it is not possible to get the property pivottables from class worksheet.

<o></o>
<o></o>
My real goal is to use an array to specify more than one value to be used in the filter. It would be something like this:



<o></o>
<o></o>
<o></o>
ActiveSheet.PivotTables(1).PivotFields("Branch").PivotFilters.Add Type:=xlValueEquals, Value1:= array(2,4,15,9)

<o></o>
<o></o>
I really thank any help I get to solve this problem. <o></o>

 

[Jerry Sullivan]

Hello and Welcome,

I'm not aware of a way to filter a pivot table using an array in one VBA statement. You could use something like the code below to make all PivotItems not Visible**, then make each item in your array list Visible.

Sub Filter_PivotTable_Using_Array()
    Dim varItemList() As Variant
    Dim strItem1 As String
    Dim i As Long
    varItemList = Array("2", "4", "15", "9")
    strItem1 = varItemList(LBound(varItemList))
    With ActiveSheet.PivotTables("MyPivotTable1").PivotFields("Branch")
        .PivotItems(strItem1).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> strItem1 Then
                .PivotItems(i).Visible = False
            End If
        Next i
        For i = LBound(varItemList) + 1 To UBound(varItemList)
            .PivotItems(varItemList(i)).Visible = True
        Next i
    End With
End Sub

**Excel doesn't let you set all PivotItems.Visible = False, so one of the first steps in the code above is to make one item Visible prior to setting the others to not Visible.

Also note that the code doesn't do error handling for unexpected things like your items in the Array not being found in your data.

 

[wassita]

JS411.
Thanks for your help. I tested and it worked. It is much better and easier to edit than the one created by the macros record. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
My problem is that applying the filter item by item slows the procedure. The macro i am creating separates the data in 14 pivot tables and I think this new filter in excel 2007 would work better. <o></o>
Thanks

 

[Jerry Sullivan]

Hi, I'm not sure that I understand your last post.

My problem is that applying the filter item by item slows the procedure.

Are you referring to the way to macro steps through each item? I didn't test this on a large data set, but it took just a fraction of a second on a small data set.

If the screen is refreshing with each item, adding this at the beginning of the procedure will stop that:

     Application.Screenupdating = False

If that isn't the problem, please clarify and let me know approximately how many seconds it takes to process.

I'm also confused by:

...and I think this new filter in excel 2007 would work better

which new filter? would work better than what?

 

[RoryA]

FYI, your original 1004 error would imply there were no pivot tables on whatever sheet was active when you ran the code.

 

[wassita]

Hello.
Thanks for answering. I corrected the 1004 error activating the correct sheet and used the suggestion

 Application.Screenupdating = False

. Now it shows an error saying that there is an incorrect object definition.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
This filter I’m trying to use(pivotfilters.add Type:=xlValueEquals, Value1:=2 is supposed to work only in Excel 2007, that is why I Said it is new.
Thank you all very much

 

[Jerry Sullivan]

Have you tried adding the Application.Screenupdating = False statement to the code I posted? I'm interested to know how long it takes to run this way on your data...since it sounds like it worked for you but was too slow.

 

[wassita]

The data base i am using is not big it has 1592 rows. When the macro is ready it will built 15 pivot tables. I built just 2 of them and it took 1minute 15 seconds using

Application.ScreenUpdating=False

and 1 minute and 18 seconds without it.

 

[Jerry Sullivan]

Hi wassita,

Thank you for sending me the example file after exchanging email addresses through a PM.

The slow speed of your PivotTable filtering appears due to the way the Data Source is being referenced. For example, the PT named "GRUPO1" has the Data Source defined as: 'gcr005'!$A:$L

When I ran the code posted below, it took about 33 seconds to filter your existing PT using the array list.

After changing the Data Source to gcr005'!$A1:$L1592 , it took less than 1 second to run the same code. If the size of your data source varies, you'll probably want to add a line to your macro that defines the data source as Sheets("gcr005").Range("A1").CurrentRegion

Sub Filter_PivotTable_Using_Array2()
    Dim dblStart As Double: dblStart = Timer
    Dim varItemList() As Variant
    Dim strItem1 As String
    Dim i As Long
 
    Application.ScreenUpdating = False
    varItemList = Array("2", "4", "17", "9")
    strItem1 = varItemList(LBound(varItemList))
    With Sheets("Plan1").PivotTables("GRUPO1").PivotFields("Filial")
 
        .PivotItems(strItem1).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> strItem1 And _
                  .PivotItems(i).Visible = True Then
                .PivotItems(i).Visible = False
            End If
        Next i
        For i = LBound(varItemList) + 1 To UBound(varItemList)
            .PivotItems(varItemList(i)).Visible = True
        Next i
    End With
End Sub

The code above has one slight improvement from the earlier version (shown in red font). It checks the Visible state of the PivotItem prior to setting it to False. This made a significant difference if many PivotItems are already not-visible and working with a large data source (as in 'gcr005'!$A:$L ).

Hope this helps!

 

[wassita]

Amazing! Problem solved.
Thanks a lot<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>