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
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
VBA 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