CODEWITHSUNDEEP
vbaexcelexcel-2010pivot-table
Deepak
Deepak

Reputation: 473

How to make multiple PivotItems visible in Excel VBA

I have a Pivot table in sheet "Main". In PivotField "Report Filter" I have "Country Code" which contain 200 countries. I want to Make visible more than 1 countries from that filter using InputBox.

The problem is I need to choose minimum one country or ALL in filter manually and run this program. I can't get the correct data by doing this. I need to deselect all the countries and then I need to run.

My Code

Sub Addcountries()

Dim ws As Worksheet
Dim str1 As Variant
Dim Data As Variant
Dim pf As PivotField
Dim target As PivotTable

Set ws = Sheets("Main")
str1 = Application.InputBox("Enter the Country - comma separated")

If str1 = False Then
    MsgBox "Please Enter one Country", , "Filter Country"
    Exit Sub
Else
    If InStr(1, str1, ",") > 0 Then
        Data = Split(str1, ",")
        For i = LBound(Data) To UBound(Data)
            ws.PivotTables("MainTable").PivotFields("Country Code").PivotItems(Data(i)).Visible = True
        Next i
    Else
        ws.PivotTables("MainTable").PivotFields("Country Code").PivotItems(str1).Visible = True
    End If
End If

End Sub

Upvotes: 1

Views: 4294

Answers (2)

Shai Rado
Shai Rado

Reputation: 33682

You can loop through the PivotItems collection, and check each PivotItem.Name if it matches one of the selected countires inside Data array - you can accomplish that using the Match function.

Code

If str1 = False Then
    MsgBox "Please Enter one Country", , "Filter Country"
    Exit Sub
Else
    If InStr(1, str1, ",") > 0 Then ' more than 1 country >> create array
        Data = Split(str1, ",")
    Else ' single country
        Data = Array(str1) '<-- create array with 1 element (for Match to work)
    End If

    ' === You need a different loop, loop through all Pivot-Items, then look for a match with Data (array) ===
    Dim pi As PivotItem

    ' clear previous Filter
    ws.PivotTables("MainTable").PivotFields("Country Code").ClearAllFilters

    For Each pi In ws.PivotTables("MainTable").PivotFields("Country Code").PivotItems
        ' check if current Pivot-Item equals one of the elements of the array (use Match function)
        If Not IsError(Application.Match(pi.Name, Data, 0)) Then ' Match successful
            pi.Visible = True
        Else
            pi.Visible = False
        End If
    Next pi
End If

' rest of your code

Upvotes: 2

apb21
apb21

Reputation: 31

In order for your filter to work you need to give False Visible values to the Pivot Items you don't want to see as well as True Values to the one's you do so that you have set all the Pivot Items to True or False.

Here is some code to give you an idea;

Sub Addcountries()
    Dim ws As Worksheet
    Dim str1 As Variant
    Dim Data() As Variant
    Dim pf As PivotField
    Dim target As PivotTable
    Dim PivotItem As Object
    Dim ShowMe As Boolean


    Set ws = Sheets("Main")
    str1 = Application.InputBox("Enter the Country - comma separated")

    If str1 = False Then
        MsgBox "Please Enter one Country", , "Filter Country"
        Exit Sub
    Else
        If InStr(1, str1, ",") > 0 Then
            Data = Split(str1, ",")
        Else
            'Make Single Item Array
            ReDim Data(1)
            Data(0) = str1
        End If
        For Each PivotItem In ws.PivotTables("MainTable").PivotFields("Country Code").PivotItems
            'Default Visibility Is False
            ShowMe = False
            For i = LBound(Data) To UBound(Data)
                'Loop Through Each Item In Data To See If You Should ShowMe
                ShowMe = (PivotItem.Name = Data(i))
                If ShowMe Then
                    'Quit Early If You ShowMe
                    Exit For
                End If
            Next i
            PivotItem.Visible = ShowMe
        Next PivotItem
    End If

End Sub

Upvotes: 0

Related Questions