Excel VBA: Create Array from Filter Field Items?

Question

A report I am creating in Excel involves several very similar pivot tables needing to be specifically filtered many times (i.e. a Year-to-Date table, a Quarter-to-Date table, etc, all needing to be filtered the exact same way before exported, then filtered again, then exported, etc)

So I looked into VBA as a way of accepting a few filter criteria, then filtering multiple tables that way, before looping.

However, I'm having a very tough time properly targeting PivotTables and specific fields, as it appears an integrated Value field is targeted and filtered via code differently than, say, a "filter' field I have attached to the top of the PivotTables, where they can accept no "begins with", "contains", etc, strings. They are just checkboxes, and one or multiple can be selected.

So it's one thing for me to tell it via VBA to select one item, and having it select all but one item. The latter requires the code to target every single possible value, but not the one that I want excluded.

My idea for this, then, is to create an array from every possible existing value in this filter field, then going through a loop where each value is added to my code as a value to check.

I have some code so far:


ActiveSheet.PivotTables("QTD_Pivot_By_Category").PivotFields( _
        "[Range].[Address_1].[Address_1]").VisibleItemsList = Array( _
        "[Range].[Address_1].&", "[Range].[Address_1].&[0]", "[Range].[Address_1].&[101]" _
        , "[Range].[Address_1].&[INC]", "[Range].[Address_1].&[KRT]", _
        "[Range].[Address_1].&[LTD]", "[Range].[Address_1].&[RPO]", _
        "[Range].[Address_1].&[ INC]", "[Range].[Address_1].&[CORP]", _
        "[Range].[Address_1].&[INC.]", "[Range].[Address_1].&[LTD.]", _
        "[Range].[Address_1].&[LTEE]", "[Range].[Address_1].&[PAWS]", _

Now, if I just record this macro from actions in Excel, and do "select All", then de-select the one I don't want, it will error. It errors because it's selecting ~300 values, and while it's 'writing' this code, it errors when it hits the limit of "_" delimited breaks in one straight line of VBA code.

If my field is called "Address_1" as above, part of the range..."Range" (not sure where that's defined or why, but it works), can I get some help as to the most efficient way to define said ".VisibleItemList" as all POSSIBLE items in the list from a dynamic array rather than needing to be selected manually? This list will be different day-to-day so it can't just be a hardcoded flat list.

Ideally, also in a way that circumvents the max limit on "_" line breaks in a line of code in VBA for Excel.

If it's of any use for context, my table looks like this. See that checkbox drop-down? I want a snapshot of every updated value sitting in there to be put into an array and then iterated upon being added in a way similar to my example code:

Edit: Since that filter field's values are being pulled from a local datasource, I decided to just grab those and make an array that way! So I'm starting my code this way:


Dim OGDataRange As Range, OGDataLastRow As Long
Dim ValueArray As Variant


OGDataLastRow = Worksheets("DATA QTD").Range("U2").End(xlDown).Row
Set OGDataRange = Worksheets("DATA QTD").Range("U2:U" & OGDataLastRow)
ValueArray = OGDataRange.Value

"ValueArray" is now my array. So I need help one-by-one pulling the values of this array, and adding them to my VisibleItemList as seen above.

Thank you so much for any assistance.

Doug Coats Doug Coats · Accepted Answer · 2019-05-24T22:01:42

This might help you

Private Sub this()
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim strPVField As String

    strPVField = "this"
    Set pt = ActiveSheet.PivotTables("PivotTable1")
    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 = False
         Next pi
        pf.AutoSort xlAscending, pf.SourceName

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

borrowed from

Deselect all items in a pivot table using vba

Excel VBA: Create Array from Filter Field Items?

1 Answers