Loop Through Array and Change Filter on OLAP Pivot Table

Question

I'm pretty new to VBA so I'm looking for some help on setting up a macro that will add the values from a specific range to an array and then loop through that array to update the filters on a pivot table. However not every value might be set. An example of this would be that only 4 values are set out of a 100 so it would only loop through it 4 times and update the filter with those 4 values. I have never worked with for loops before so any guidance on this would be extremely helpful.

My code so far:

Sub Update_Filters()

    Dim PortfolioCodes As Variant
    PortfolioCodes = Sheets("Configuration Sheet").Range("C7:C45").Value

    Sheets("List").PivotTables("List").PivotFields( _
        "[Portfolio].[Portfolio Code].[Portfolio Code]").VisibleItemsList = Array( _
        "[Portfolio].[Portfolio Code].&[ABC1]", "[Portfolio].[Portfolio Code].&[ABC2]", _
        "[Portfolio].[Portfolio Code].&[XYZ1]", "[Portfolio].[Portfolio Code].&[XYZ2]")
End Sub

I don't get it, you want your procedure to show the data for all 4 values at the same time or filter 1 at a time and do something? — Damian
– Damian, Commented Nov 26, 2019 at 16:11

Damian · Accepted Answer · 2019-11-26 20:48:48Z

1

Here, the two possible answers to your question:

Option Explicit
Sub Update_Filters()

    'this code will filter one portfolio code at a time
    Dim LastRow As Long
    With ThisWorkbook.Sheets("Configuration Sheet")
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        Dim PortfolioCodes As Variant
        PortfolioCodes = .Range("C7:C" & LastRow).Value
    End With

    For i = LBound(PortfolioCodes) To UBound(PortfolioCodes)
        ThisWorkbook.Sheets("List").PivotTables("List").PivotFields( _
        "[Portfolio].[Portfolio Code].[Portfolio Code]").VisibleItemsList = _
            Array("[Portfolio].[Portfolio Code].&[" & PortfolioCodes(i) & "]")
        'some code
    Next i

End Sub
Sub Update_FiltersALL()

    'This code will filter all the portfolio codes in your range
    With ThisWorkbook.Sheets("Configuration Sheet")

        Dim LastRow As Long
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

        Dim i As Long
        i = 1

        ReDim PortfolioCodes(1 To LastRow - 6) As String
        Dim C As Range
        For Each C In .Range("C7:C" & LastRow)
            If Not IsEmpty(C) Then
                PortfolioCodes(i) = "[Portfolio].[Portfolio Code].&[" & C.Value & "]"
                i = i + 1
            End If
        Next C
    End With

    ThisWorkbook.Sheets("List").PivotTables("List").PivotFields( _
    "[Portfolio].[Portfolio Code].[Portfolio Code]").VisibleItemsList = PortfolioCodes

End Sub

edited Nov 26, 2019 at 20:48

answered Nov 26, 2019 at 16:27

Damian

5,1741 gold badge12 silver badges22 bronze badges

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

7 Comments

Gil Over a year ago

I'm trying to use the second version of the code you provided which updates all, however it's throwing a subscript out of range error. Is there something that I'm over looking?

Damian Over a year ago

@Gil when trying to filter? might be some empty values on the range? or some codes which aren't on the olap?

Gil Over a year ago

Yes there are empty values in the range which is why I want the range to stop reading at the point where the next cell is empty

Damian Over a year ago

@Gil try my edit.. it will ignore empty cells and loop only to the last row (will ignore empty cells that might be in between your range)

Gil Over a year ago

I'm getting a new error "Compile Error: Constant expression required", which is highlighting the LastRow in Dim PortfolioCodes(1 To LastRow - 6) As String

|

Collectives™ on Stack Overflow

Loop Through Array and Change Filter on OLAP Pivot Table

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related