Dynamic Pivot Table - Uncheck Blanks VBA

[rikvny02]

My data is always changing but I never need blanks within the filter. Id like to uncheck blanks each and every time I refresh my pivot table. The below code works great within a table but, It will not work on a pivot table. I receive a 400 error code. Can someone help me manipulate the code to work with a pivot table.

Sub FilterDynamicRng1() 'Excel VBA dynamic range
Range("a1", Range("a" & Rows.Count).End(xlUp)).AutoFilter 1, "<>"
End Sub

 

[Z51]

By recording a macro while filtering (blank) from a Pivot Table, I get...

ActiveSheet.PivotTables("PivotTable1").PivotFields("Pay Code").PivotFilters.Add2 Type:=xlCaptionDoesNotEqual, Value1:=""

 

[rikvny02]

z51 - your code absolutely works. However, every time I run the macro I receive a error. Any Ideas?

 

[Z51]

Try adjusting "ActiveSheet" to your sheet name, e.g. Sheets("Sheet1")
As well as the PivotTable name (might not be "PivotTable1"
And finally the PivotFields name to your field name.

 

[rikvny02]

Still receiving the error. I've used every combination I can possibly think of. the PivotFields("row labels") was used as well as pivotfields("Pay Code").

Sub pcfilter() 'Excel VBA dynamic range
Sheets("Report - Hours by Pay Code").PivotTables("PivotTable3").PivotFields("row labels").PivotFilters.Add2 Type:=xlCaptionDoesNotEqual, Value1:="(blank)"

End Sub

 

[rikvny02]

The below code work by hiding the blanks but id rather see the blank UNCHECKED


With Sheets("report - hours by pay code").PivotTables("PivotTable3").PivotFields("Pay Code")
.PivotItems("(blank)").Visible = False
End With