Filter Pivot Table UiPath

Help Studio
, , , , ,
1

Hello everyone,

I would like to know how can I filter a Pivot Table with two or more values in a column.
Do I have to use VBA code ?
If yes, what is the code to do that ?

Thanks in advance!

2

Hi @Maria_Ines_Almeida

It would be very helpful if you can share a sample input excel file.
A VBA approach can be worked out.

Also, LINQ would be useful for creating the pivot table from the raw data and then filtering it according to the requirements.

3

@Maria_Ines_Almeida Mostly it can be done using linq or read the data to a data table and filter using Filter Data Table Activity. If possible please share the sample input data

4

Hi @Maria_Ines_Almeida

you can use this example

remember to change the values on this line (table id and the column name)
Set myPivotField = ActiveSheet.PivotTables("PivotTable1").PivotFields("State")

and the values to filter line
FilterArray = Array("AZ", "NY")

then paste the code in a text file and call it like this

image
image1169×322 23.1 KB 

Function FilterMultipleArray(sheetName as string)
FilterArray = Array("AZ", "NY")

Dim myPivotField As PivotField
Dim ws as worksheet
Set ws = ActiveWorkbook.Sheets(sheetName)
Set myPivotField = ws.PivotTables("PivotTable1").PivotFields("State")
myPivotField.ClearAllFilters
myPivotField.EnableMultiplePageItems = True

numberOfElements = UBound(FilterArray) - LBound(FilterArray) + 1

If numberOfElements > 0 Then
    With myPivotField
        For i = 1 To myPivotField.PivotItems.Count
        j = 0
        Do While j < numberOfElements
            If myPivotField.PivotItems(i).Name = FilterArray(j) Then
                myPivotField.PivotItems(myPivotField.PivotItems(i).Name).Visible = True
                Exit Do
            Else
                myPivotField.PivotItems(myPivotField.PivotItems(i).Name).Visible = False
            End If
            j = j + 1
        Loop
        Next i
    End With
End If

ActiveWorkbook.Save

End Function