Excel宏VBA使用通配符过滤 [英] excel macro vba Filtering using wildcards
问题描述
我需要过滤数据透视表. 我需要取消选择所有以"A"开头的项目.和"H". 并保持所有其他选择.
I need to filter in pivot. I need to deselect all items starting with "A." and "H." and keep everything else selected.
这些项目的范围是: A.(3-13个字符) B.(3-13个字符) 一路走到 Z.(3-13个字符)
the items ranges from: A.(3-13 characters) B.(3-13 characters) all the way to Z.(3-13 characters)
原始数据也从50-500行变化(我今天的数据只能有50行-明天我可能有500行以上)
the raw data also changes from 50-500 rows (I can have a data with only 50 rows today - then tomorrow I may have over 500)
我当前的代码有效:(通过输入该列上显示的所有可能的项目-大约300多个项目),它长了,但是可以工作. 最近,我得到了更多添加到该列表中的项目,而且我知道我们可以使用通配符.
my current code works: (by entering all possible items which appears on that column - roughly over 300 items) its long but it works. lately I've been getting more items adding to that list, and I know we can use wild cards.
[MY CURRENT CODE]
ActiveSheet.PivotTables("PivotTable1").PivotFields("column").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("column"). _
EnableMultiplePageItems = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields("column")
.PivotItems(" PLACE ITEM HERE ").Visible = False
.PivotItems
.PivotItems
.PivotItems [repeat over 300 times - changing the " PLACE ITEM HERE " with the items on the column]
End With
我只能将300条以上的行减少到大约5-10行,我在想类似(如下)的问题,这也解决了我的新物品不在列表中的问题:
I can cut down the 300+ lines to about 5-10 lines only, I was thinking about something like (below) it would also fix my problem of having new items that are not yet on my list:
ActiveSheet.PivotTables("PivotTable1").PivotFields("column").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("column"). _
EnableMultiplePageItems = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields("column")
.PivotItems("A.*").Visible = False
.PivotItems("H.*").Visible = False
End With
但这不起作用
推荐答案
尝试一下:
With ActiveCell.PivotTable.PivotFields("Column")
For i = 1 To .PivotItems.Count
If .PivotItems(i).Name like "A.*" or .PivotItems(i).Name like "H.*" Then
.PivotItems(i).Visible = True
else
.PivotItems(i).Visible = False
End If
Next i
End With
这篇关于Excel宏VBA使用通配符过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!