Amend Pivot Table VBA to remove multiple Items

FrancesBarker — Mon, 27 Jan 2025 10:13:32 GMT

Hi,

I created a code stage in my Excel VBO Extended to Select all then exclude certain values from the pivot table filter. However I am not a coder and would like to be able to pass in a variable no of values to exclude in a collection.

Here is my current code that excludes 2 values. But I may have 3 or 4 or 1. Please can someone kindly help me? Thanks.

Re: Amend Pivot Table VBA to remove multiple Items

plnarayana777 — Sat, 15 Feb 2025 14:27:29 GMT

Hi @FrancesBarker

Where you able to get solution?

I would suggest to check the same on chatgpt or similar

I did gave a try with Chatgpt and got response and looks fine to give try on below solution

Got it! Since you're using Blue Prism, you'll need to ensure that ExcludeCollection is properly passed as a collection in your Blue Prism workflow. Blue Prism collections work like tables with multiple rows, so you'll need to iterate over them inside the Code Stage.

Updated Code for Blue Prism Code Stage

Dim ws As Object
Dim pt As Object
Dim pf As Object
Dim excludeItem As String
Dim i As Integer

ws = GetWorksheet(Handle, Workbook, Worksheet)
ws.Activate()

' Reference the PivotTable and PivotField
Set pt = ws.PivotTables(PivotTableName)
Set pf = pt.PivotFields(Field)

' Select All Items First
pf.ClearAllFilters

' Loop through the Blue Prism collection ExcludeCollection
For i = 1 To ExcludeCollection.Rows.Count
    excludeItem = ExcludeCollection.Rows(i).Item("Exclude") ' Adjust column name as needed
    pf.PivotItems(excludeItem).Visible = False
Next i

Key Points

Uses a Blue Prism collection (ExcludeCollection) where each row contains an item to exclude.
Retrieves values dynamically using ExcludeCollection.Rows(i).Item("Exclude"). Ensure "Exclude" matches the column name in your collection.
Loops through the collection dynamically, handling any number of values.

Blue Prism Setup

Before the Code Stage, ensure you have a collection (ExcludeCollection) with a column named "Exclude" containing values to be filtered out.
Pass ExcludeCollection as an input parameter to the Code Stage.

Would you like guidance on setting up the collection in Blue Prism?

topic Amend Pivot Table VBA to remove multiple Items in Product Forum

Amend Pivot Table VBA to remove multiple Items

Re: Amend Pivot Table VBA to remove multiple Items

Updated Code for Blue Prism Code Stage

Key Points

Blue Prism Setup