Excel VBA-Privot表过滤器多个条件 [英] Excel VBA - Privot table filter multiple criteria
本文介绍了Excel VBA-Privot表过滤器多个条件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用多个条件过滤数据透视表.我已经检查了其他帖子,但出现错误""Range类的AutoFiler方法失败",运行时:
I am trying to filter a pivot table with multiple criteria. I've check other posts, but I am getting the error "AutoFiler method of Range class failed" when running:
Range("g41").Select
Selection.AutoFilter field:=1, Criteria1:=Array( _
"101", "103"), Operator:=xlFilterValues
以下方法有效,但是有很多项目要过滤true/false
The following works, but there are quite a long number of items to filter true/false
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Value")
.PivotItems("101").Visible = True
.PivotItems("103").Visible = True
.PivotItems("105").Visible = False
End With
有没有更有效的方法?
推荐答案
您可以尝试以下代码:
Option Explicit
Sub FilterPivotItems()
Dim PT As PivotTable
Dim PTItm As PivotItem
Dim FiterArr() As Variant
' use an array to select the items in the pivot filter you want to keep visible
FiterArr = Array("101", "105", "107")
' set the Pivot Table
Set PT = ActiveSheet.PivotTables("PivotTable3")
' loop through all Pivot Items in "Value" Pivot field
For Each PTItm In PT.PivotFields("Value").PivotItems
If Not IsError(Application.Match(PTItm.Caption, FiterArr, 0)) Then ' check if current item is not in the filter array
PTItm.Visible = True
Else
PTItm.Visible = False
End If
Next PTItm
End Sub
这篇关于Excel VBA-Privot表过滤器多个条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文