Pivot table manipulation VBA code

[rajaniesh]

Hi,

I have a pivot table in a sheet. It has hours into it. I want to select All hours and then uncheck zero and blank hours. I am using the below vba code but it does nto work as expected.


Sub FilterPivotHours()
currentWorkBookName = ThisWorkbook.Name
Workbooks(currentWorkBookName).Sheets("Pivot").Activate
ActiveSheet.PivotTables("PivotTable1").PivotFields("Hours"). _
CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Hours")
On Error Resume Next
.PivotItems("(All)").Visible = True
.PivotItems("0").Visible = False
.PivotItems("(blank)").Visible = False
On Error GoTo 0
End With
End Sub

Regards
Rajaniesh

 

[Dryver14]

try commenting out this line

.PivotItems("(All)").Visible = True

 

[ParamRay]

Sub FilterPivotHours()
  Dim pvt As PivotTable
  Dim pvf As PivotField
  
  Set pvt = ThisWorkbook.Worksheets("Pivot").PivotTables("PivotTable1")
  Set pvf = pvt.PivotFields("Hours")
  pvf.ClearAllFilters
  
  On Error Resume Next
  pvf.PivotItems("(blank)").Visible = False
  pvf.PivotItems("0").Visible = False
End Sub

 

[rajaniesh]

Thank YOU sir This works fine!!

 

[rajaniesh]

What we need to change in the code if we need to modify the code so that we can only select two values AD and Ba but deselect all other values in the below code


Sub FilterPivotAccountFilter()
Dim pvt As pivotTable
Dim pvf As PivotField

Set pvt = ThisWorkbook.Worksheets("Pivot").PivotTables("PivotTable1")
Set pvf = pvt.PivotFields("Account Filter")
'pvf.ClearAllFilters

On Error Resume Next

pvf.PivotItems("Ba").Visible = True
pvf.PivotItems("AD").Visible = True
pvf.PivotItems("(All)").Visible = False

End Sub

 

[rajaniesh]

Nevermind I got the answer from this link https://www.reddit.com/r/excel/comments/1aspuf/vba_to_only_select_certain_items_in_a_pivot_field/