How to use VBA to apply filter to a pivot table

[phroureo]

Hello everyone, I have a unique need:

I need to be able to filter out a certain set of numbers (account numbers from a chart of accounts, if that helps). In this case, let's say I need to uncheck the "Filter" for "Accounts" if the account number is between 40000 and 60000.

I tried the following code:

'filters out the non-revenue accounts    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account")
        For i = 40000 To 60000
        .PivotItems(i).Visible = False
        Next i
    End With

but it didn't work. I presume it's because there is not necessarily a value for every number between 40000 and 60000? Or is it because they are stored as text values in the filter field, and can't be compared to numbers?

I'm not sure that this problem has a solution, but any help would be much appreciated.

[mancubus]

try recording a macro while manually doing this.

it seems 40000 thru 60000 are pivotitems' names.

it's hard to code without seeing the workbook but something like this may be of help:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account")
    For i = 1 To .PivotItems.Count
        piName = .PivotItems(i).Name
        If CLng(piName) >= 40000 And CLng(piName) <= 60000 Then
            .PivotItems(i).Visible = False
        End If
    Next i
End With

[phroureo]

See, the issue was that the items didn't all exist (For instance, I know there was a blank space in my data between 41000 and 43000). However, in the future, there may have been more items in the set than were in my sample data, so I had to come up with some way to include those items.

I ended up putting an "On Error Resume Next" in after the "For" and that solved my problem and made it work.