CODEWITHSUNDEEP
vbaexcel
jhovyn
jhovyn

Reputation: 265

VBA - How to hide pivot fields?

I want to remove a certain field in my pivot using this code below but it is not working. What's wrong with this code?

Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim rows As Variant

Set pt = ActiveSheet.PivotTables(1)

'Remove rows not needed
rows = Array("A", "B", "C", "D")

For Each pt In ActiveSheet.PivotTables
   pt.PivotFields(rows(0)).Orientation = xlHidden
   pt.PivotFields(rows(1)).Orientation = xlHidden
   pt.PivotFields(rows(2)).Orientation = xlHidden
   pt.PivotFields(rows(3)).Orientation = xlHidden
Next pt

Upvotes: 0

Views: 2364

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

See comments inside:

Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim PTrows As Variant '<< DO NOT USE ROWS WHICH IS RESERVED OBJECT INSTRUCTION

'Set pt = ActiveSheet.PivotTables(1) << YOU DON'T NEED IT IF YOU HAVE A LOOP

'Remove rows not needed
PTrows = Array("A", "B", "C", "D")

For Each pt In ActiveSheet.PivotTables
   pt.PivotFields(PTrows(0)).Orientation = xlHidden
   pt.PivotFields(PTrows(1)).Orientation = xlHidden
   pt.PivotFields(PTrows(2)).Orientation = xlHidden
   pt.PivotFields(PTrows(3)).Orientation = xlHidden
Next pt

Upvotes: 2

Related Questions