remove/delete/hide/exclude all (Blanks) in a pivot table macro

[mju]

Below is a sample of my code to remove all (blanks) in a pivot table.

With ActiveSheet.PivotTables("PivotTable1").PivotFields("TRAD_PARTNER ")
.PivotItems("(blank)").Visible = False
End With
it works fine it there balnks in the pivot table.
However, sometimes if the data set does not have any (blank), the macro
generates a Run-time 1004 error. (Unable to get the PivotItems Property of
the PivotField class)
Is there a that i can skip the code if there are no (blank)?
I tried If statement but it did not work.

Thanks alot people-

 

[Bob Umlas]

On Error Resume Next
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TRAD_PARTNER ")
.PivotItems("(blank)").Visible = False
End With

Bob Umlas
Excel MVP

 

[r]

Sub test()
Dim pi As PivotItem
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TRAD_PARTNER ")
For Each pi In .PivotItems
If pi.Caption = "(blank)" And pi.Visible = True Then
pi.Visible = False
Exit For
End If
Next
End With
End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[mju]

thanks Alot!!!!

On Error Resume Next
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TRAD_PARTNER ")
.PivotItems("(blank)").Visible = False
End With

Bob Umlas
Excel MVP