0

When recording the deselection of the "(Blank)" items I get an explicit mention of all items.

ActiveChart.PivotLayout.PivotTable _
.PivotFields("[Table1].[Field1].[Field1]").VisibleItemsList = _
                                           Array("[Table1].[Field1].&[Item1]", _
                                                 "[Table1].[Field1].&[Item2]", _
                                                 "[Table1].[Field1].&[Item3]", _
                                                 "[Table1].[Field1].&[Item4]", _
                                                 "[Table1].[Field1].&[Item5]", _
                                                 "[Table1].[Field1].&[Item6]")

But I can't mention the items since I don't know them beforehand (and they are located in the data model).

The code should be along the lines of:

With ActiveChart.PivotLayout.PivotTable 
    .PivotFields("[Table1].[Field1].[Field1]").VisibleItemsList = Array("")
    .PivotFields("[Table1].[Field1].[Field1]").PivotItems("(blank)").Visible = False
End With

EDIT: Apparently, .PivotItems("(blank)").Visible = False should be doing exactly what I need, but it is not... (Runtime '1004': Unable to get the PivotItems property of the PivotField class)

TCN
  • 1,571
  • 1
  • 26
  • 46
  • 1
    Note, instead of editing the answer into the question, it would be more fitting with the Q&A nature of this site to post an answer to your own question. – Mathieu Guindon Sep 16 '16 at 19:38
  • I was not clear in my phrasing: it should be doing what I need, but it is not. – TCN Sep 16 '16 at 19:41
  • Meanwhile, I found a solution/workaround and I did post it as answer. Thanks for the tip. – TCN Sep 16 '16 at 20:59

2 Answers2

1

I found a solution/workaround:

    Dim PivItem            As PivotItem
    Dim PivFieldsArray()   As Variant
    Dim i                  As Integer

    With ActiveChart.PivotLayout.PivotTable.PivotFields("[Table1].[Field1].[Field1]")
        For Each PivItem In .PivotItems
            ReDim Preserve PivFieldsArray(0 To i)
            If PivItem.name <> "[Table1].[Field1].&" Then
                PivFieldsArray(i) = PivItem.name
                i = i + 1
            End If
        Next
        .VisibleItemsList = PivFieldsArray
    End With
TCN
  • 1,571
  • 1
  • 26
  • 46
1

It's been a while, but I had the same issue and just found a different solution that worked in my situation.

Rather than record 'deselecting blank' which I did at first but didn't work, I recorded a 'Label Filter' with "does not equal:" (with nothing entered in the text box). That then gave me the following code (slightly modified since I'm using a with statement):

.PivotFields("[Range].[Street Direction Code].[Street Direction Code]").PivotFilters.Add2 _
        Type:=xlCaptionDoesNotEqual, Value1:=""
Jay
  • 2,456
  • 2
  • 13
  • 5
  • Looping is bad for performance. I haven't tested your solution, but I can see it really helping someone with a large table. – TCN Feb 02 '23 at 10:18