CODEWITHSUNDEEP
excelpowerpivotvba
Fernando Talvik
Fernando Talvik

Reputation: 1

How to remove all column fields in a Pivot Table ( Power Pivot)?

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!!

Upvotes: 0

Views: 3020

Answers (1)

Olly
Olly

Reputation: 7891

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

Upvotes: 1

Related Questions