VBA Pivot Table make visible values from a field based on a range

[k0023382]

A very simple thing, I do not get my head around.

On a given cell, I could have A1= "1:3:7", it means 3 ID locations
Ideally I would to transform this string onto an array in VBA.

Array(0)="1"
Array(1)="3"
Array(2)="5"

The ":" is the character that separate each element. Sometimes the string has only 1 item no more separator, others it is long with more than 20 items on it. Items could be text or numbers.
And finally apply those to a field on a Pivot table called Location

Below the code

Public Sub PV_MutipleItems2()

Dim arr() As String

' Extract his value from the cell for Location
Dim strP As String: strP = "1:3:5" 'test, originally from Range("A1").value
Dim strSpecialC As String: strSpecialC = ":"

arr() = Split(strP, strSpecialC)

If UBound(arr) = 0 Then
        'make visible only 1 item
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Location")
            .ClearAllFilters
            .PivotItems.Name.Visible = arr(0)
        End With
    Else
        'make visible more than 1 item
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Location") '.PivotFilters
            .ClearAllFilters
            For Each Pi In .PivotItems
                For i = LBound(arr) To UBound(arr)
                    If Pi.Name = arr(i) Then
                            Pi.Visible = True
                        Else
                            Pi.Visible = False
                    End If
                Next i
            Next
        End With
End If

End Sub

 

[k0023382]

A little mistake,

instead of

       'make visible only 1 item
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Location")
            .ClearAllFilters
            .PivotItems.Name.Visible = arr(0)
        End With

it shoud be

'make visible only 1 item
ActiveSheet.PivotTables("PivotTable2").PivotFields("Location").CurrentPage = arr(0)