如何删除数据透视表（Power Pivot）中的所有列字段？ [英] How to remove all column fields in a Pivot Table ( Power Pivot)?

问题描述

每个人。

我正在使用Sub ClearColumns清除数据透视表中列区域中的所有字段。

I was using the Sub ClearColumns to clear all field in the column area from a Pivot Table.

Sub ClearColumns()

Dim PT As PivotTable, PF As PivotField

Set PT = ActiveSheet.PivotTables("Tb1")

    With PT
    For Each PF In .ColumnFields
        PF.Orientation = xlHidden
    Next PF
End With
Set PT = Nothing

End Sub

但是现在我开始使用PowerPivot，代码不起作用。我认为这些字段不再是PivotField，而是CubeFields。可以在上面创建相同的宏，但是对于Cubefields？我尝试了不同的方法，但无法使其正常工作。

But now that i started using PowerPivot, the code doesnt Work. I think the fields are no longer PivotField, but CubeFields. It Is possible to make the same macro above, but for Cubefields? I tried different things, but i cant make it work.

Sub ClearColumnsPP()

Dim PT As PivotTable, PF As CubeFields
Dim strSource As String
Dim strName As String

    Set PT = ActiveSheet.PivotTables("Tb1")

With PT

    For Each PF In .ColumnFields
        strSource = PF.SourceName
        strName = PF.Name
        PF(strSource, strName).Orientation = xlHidden
    Next PF
End With

    Set PT = Nothing

    End Sub

谢谢！ ！

推荐答案

您需要检查CubeField的方向，并且只有在它是Column字段时才设置为hidden：

You need to check the orientation of the CubeField, and only set to hidden if it is a Column field:

Sub foo()
    Dim pt As PivotTable
    Dim cf As CubeField

    Set pt = ActiveSheet.PivotTables("Tb1")
    For Each cf In pt.CubeFields
        If cf.Orientation = xlColumnField Then cf.Orientation = xlHidden
    Next cf
End Sub

这篇关于如何删除数据透视表（Power Pivot）中的所有列字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！