How to remove all column fields in a Pivot Table (

Question

everyone.

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

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

Thanks!!

Answer 1

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