Power Pivot VBA filter reference range of cells

Question

I am trying to have my power pivot filter reference to a range of values in multiple cells. For example, if my filter has values A,B,C,D I want it to look into cells A1:A10 to see what values are there. If A,B exists in A1:A10 I want the filter to show only A and B (each cell only holds one value ex. A1: A, A2: B, A3: "", etc.)

With the code below I am only able to reference one value.

Sub SelectKey()


    Dim key As String

    key = Sheets("Sheet3").Range("R1").Value

    Sheets("data").PivotTables("PivotTable6").PivotFields("[v_cprs_dashboard_metrics].[metric_key].[metric_key]").VisibleItemsList = Array("[v_cprs_dashboard_metrics].[metric_key].&[" & key & "]")

End Sub

This works for regular pivot table but not powerpivot

With Worksheets("data").PivotTables("PivotTable6").PivotFields("metric_key")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Sheets("Sheet3").Range("range1"), PI.Name) > 0
        Next PI
    End With

try Dim key() as String key = Split(Sheets("Sheet3").Range("R1").Value,","") — Scott Holtzman
try as Variant ... loads the string into an array itself. if that doesn't work, see the answer i posted below — Scott Holtzman

Scott Holtzman Scott Holtzman · Accepted Answer · 2016-06-27T16:53:19

There may be a better way to do this, but from my experience, the only way to accomplish this is to loop through the PivotItems in the PivotField and check the names against the criteria.

Sub SelectKey()

Dim key() As String

key = Split(Sheets("Sheet3").Range("R1").Value, ",") 'load comma separated string to array

Dim pi As PivotItem
For Each pi In Sheets("data").PivotTables("PivotTable6").PivotFields("[v_cprs_dashboard_metrics].[metric_key].[metric_key]").PivotItems

    Dim i As Integer
    For i = LBound(key) To UBound(key) 'loop through array

        If pi.Name = key(i) Then
            pi.Visible = True
            Exit For
        Else
            pi.Visible = False
        End If

    Next

Next

End Sub

Power Pivot VBA filter reference range of cells

1 Answers