使用通配符进行excel宏vba过滤

I need to filter in pivot. I need to deselect all items starting with "A." and "H." and keep everything else selected.

我需要在主元中过滤。我需要取消所有以“a”和“h”开头的项目，并保持所有其他项目都被选中。

the items ranges from: A.(3-13 characters) B.(3-13 characters) all the way to Z.(3-13 characters)

项目范围从:A。(3-13字符)B。(3-13个字符)一直到Z。(3-13字符)

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)

原始数据也会从50-500行变化(我今天可以有一个只有50行的数据，那么明天我可能有超过500行)

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.

我当前的代码可以工作:(通过输入列中出现的所有可能的项——大约超过300项)它很长，但是它可以工作。最近我得到了更多的项目添加到这个列表，我知道我们可以使用外卡。

[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

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:

我可以把300多行减少到只有5-10行，我在想(以下)这样的事情，它也可以解决我的问题，让我的新项目没有在我的清单上:

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

but this is not working

但这行不通

1 个解决方案

#1

Try this:

试试这个:

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