CODEWITHSUNDEEP
vbaexcel
Chris2015
Chris2015

Reputation: 1050

Deselect all items in a pivot table using vba

Can some quicly explain the way to deselect all items in a newly created pivot table so that I can go back and select only one or two items? I tried the following:

.PivotItems("(Select All)").Visible = False

Thanks.

Upvotes: 8

Views: 87749

Answers (5)

SergioBetween
SergioBetween

Reputation: 1

Well.

Because you have not how to hide all, because, always you need to have 1 item visible

I do this:

I start hiding the first field, and before go to the next, i show all fields i need visible, then, i go to the secont item, and hide, and again show all items i want, and so on. Then, always will be visible any field, and wont have error.

After the loop, i again try to show all fields i want.

With ActiveSheet.PivotTables("TablaD2").PivotFields("Entity")

    Dim i As Long

    For i = 1 To .PivotItems.Count

    .PivotItems(i).Visible = False

    .PivotItems("ARG").Visible = True
    .PivotItems("BRL").Visible = True
    .PivotItems("GCB").Visible = True
    .PivotItems("MEX").Visible = True
    Next
    .PivotItems("ARG").Visible = True
    .PivotItems("BRL").Visible = True
    .PivotItems("GCB").Visible = True
    .PivotItems("MEX").Visible = True



End With

Upvotes: 0

gilad
gilad

Reputation: 21

I've found that looping through each data item takes a lot of time, what you can do if you want to filter on a single item in a pivot without looping through all items is use the following code:

ActiveSheet.PivotTables("Your Pivot Name").PivotFields("Your Field Name").ClearAllFilters
ActiveSheet.PivotTables("Your Pivot Name").PivotFields("Your Field Name").PivotFilters.Add _
    Type:=xlCaptionEquals, Value1:="Your string here"

this is basically a label filter but it worked for me.

Upvotes: 2

asun
asun

Reputation: 1135

This is how I do for custom filter selection. May be slower due to double looping.

Dim toSelect(1 To 3) As String

toSelect(1) = "item1"
toSelect(2) = "item2"
toSelect(3) = "item3"


For Each pvItem In objField.PivotItems
    For Each st In toSelect
        If pvItem.Value = st Then
            pvItem.Visible = True
            Exit For
        Else
            pvItem.Visible = False
        End If
    Next
Next

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

Check the following out. Select data for specific field name. Please do note that you have to at least select one item by default. And also do not forget that if you want to hide items, Only contiguous items in a PivotTable Field can be hidden. Perhaps at page load, or worksheet open or any of your other sub trigger, you could select a particular items to be selected based on a specific field. Then allow your code to proceed with anything else.

Sub specificItemsField()
Dim pf As PivotField
Dim pi As PivotItem
Dim strPVField As String

strPVField = "Field Name"
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields(strPVField)
Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next
    pf.AutoSort xlManual, pf.SourceName
     For Each pi In pf.PivotItems
         pi.Visible = True
     Next pi
    pf.AutoSort xlAscending, pf.SourceName

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Upvotes: 1

Daniel
Daniel

Reputation: 13122

This is probably the closest you can get to what you want:

Dim i As Long
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
    .PivotItems(i).Visible = False
Next

This will make the very first option the only selected option (assuming this is within a with that points to the pivotfield). If you know what you want before hand... modify accordingly.

Upvotes: 10

Related Questions