PDA

View Full Version : How to use VBA to apply filter to a pivot table



phroureo
11-30-2015, 04:56 PM
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
12-02-2015, 03:07 AM
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
12-03-2015, 08:07 AM
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.