Deselect all items in a pivot table using vba

Question

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.

You always need to have one cell selected, so what if you just select some random cell instead? — PowerUser
– PowerUser, Commented Dec 7, 2012 at 17:42
I only want two particular cells selected so I wanted to unselect all of them and then only select the two I want. — Chris2015
– Chris2015, Commented Dec 7, 2012 at 17:45
Well, there's no real need to deselect your current selection first. Could you just skip straight to selecting those two cells? — PowerUser
– PowerUser, Commented Dec 7, 2012 at 17:47
When I create the pivot table it starts with all items select. I need to unselect the items I don't what to start. I can do this individually with .PivotItems("ItemWhatever").Visible = False, but I am not sure how to unselect all items at once. — Chris2015
– Chris2015, Commented Dec 7, 2012 at 17:51

Daniel · Accepted Answer · 2012-12-07 17:53:22Z

10

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.

answered Dec 7, 2012 at 17:53

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris2015 Over a year ago

Here is what I went with. Worked Great. Thanks! Dim i As Integer With ActiveSheet.PivotTables("PivotTable1").PivotFields("Business Area") .Orientation = xlRowField .Position = 1 For i = 2 To .PivotItems.Count .PivotItems(i).Visible = False .PivotItems("Fire and EMS - Uniform").Visible = True .PivotItems("Police - Uniform").Visible = True .PivotItems(i - 1).Visible = False Next i End With

Chris2015 Over a year ago

Or Rather Dim i As Integer With ActiveSheet.PivotTables("PivotTable1").PivotFields("Business Area") .Orientation = xlRowField .Position = 1 For i = 2 To .PivotItems.Count .PivotItems(i - 1).Visible = False .PivotItems("Fire and EMS - Uniform").Visible = True .PivotItems("Police - Uniform").Visible = True .PivotItems("(blank)").Visible = False Next i End With

dra_red Over a year ago

I had used this approach and found it to still be too slow but by turning the manualupdating to true (ie PivotTable.ManualUpdate = True) it made it bearable. It is described here:stackoverflow.com/questions/764504/…

gilad · Accepted Answer · 2015-01-26 14:27:17Z

2

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.

answered Jan 26, 2015 at 14:27

gilad

211 bronze badge

Comments

bonCodigo · Accepted Answer · 2012-12-07 18:04:22Z

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

asun · Accepted Answer · 2014-03-27 09:42:49Z

0

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

answered Mar 27, 2014 at 9:42

asun

1,1452 gold badges8 silver badges6 bronze badges

Comments

SergioBetween · Accepted Answer · 2019-09-03 17:49:13Z

0

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

answered Sep 3, 2019 at 17:49

SergioBetween

1

2 Comments

SergioBetween Over a year ago

In this example, i want the fields with description ARG BRL GCB MEX. ALL OTHERS WILL BE HIDDEN.

Alan Over a year ago

Wow, that "must have at least one item selected" gotcha was not obvious at all... especially since the UI for a pivot chart lets you "deselect all" :facepalm:

Collectives™ on Stack Overflow

Deselect all items in a pivot table using vba

5 Answers 5

3 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related