代码之家 › 专栏 › 技术社区 › Deepak

使轴项目在固定范围内可见

excel-2010 excel vba

Deepak · 技术社区 · 6 年前

我在“代码”表中有一个表格。在C列中,我有一些国家代码,如DE、FR、GB。。。我只想选择所有值,并使at在主工作表中可用的透视表中可见。我使用了下面的代码,但没有工作,也没有显示错误。帮我修改代码。

Sub pivot()
Dim wst,ws As Worksheet
Dim arr1() As String
Dim j As Long

Set wst = Sheets("Code")
LastCol = wst.Cells(wst.Rows.Count, 3).End(xlUp).Row
ReDim Preserve arr1(1 To LastCol)
  For j = 1 To LastCol
     arr1(j) = wst.Cells(j, 3).Value
Next j

Set ws = Worksheets("Main")    
 ws.PivotTables("MainTable").PivotFields("Country Code").ClearAllFilters
    With ws.PivotTables("MainTable").PivotFields("Country Code")
      For Each pi In .PivotItems
        pi.Visible = InStr(1, arr1, pi.Name) > 0
      Next
End With

End Sub

1 回复 | 直到 6 年前

QHarr 6 年前

在这件事上,我赢了。我假设设置ws=图纸(“主”)

Option Explicit

Sub pivot()

    Dim wst As Worksheet
    Dim arr1() As String
    Dim j As Long
    Dim lastRow As Long

    Dim ws As Worksheet
    Set ws = Sheets("Main")
    Set wst = Sheets("Code")
    lastRow = wst.Cells(wst.Rows.Count, 3).End(xlUp).Row
    ReDim Preserve arr1(1 To lastRow)

    For j = 1 To lastRow
        arr1(j) = wst.Cells(j, 3).Value
    Next j

    Worksheets("Main").PivotTables("MainTable").PivotFields("Country Code").ClearAllFilters
    Dim Pi As PivotItem

    Dim pvt As PivotTable
    Set pvt = ws.PivotTables("MainTable")

    With pvt.PivotFields("Country Code")

        For Each Pi In .PivotItems
            If IsError(Application.Match(Pi.Name, arr1, 0)) Then Pi.Visible = False
        Next Pi

    End With

End Sub

推荐文章