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:)-

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

Bob Umlas
Excel MVP

"mju" wrote in message
...
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:)-

thanks Alot!!!!

"Bob Umlas" wrote:

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

Bob Umlas
Excel MVP

"mju" wrote in message
...
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:)-

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/...ternative.html


"mju" wrote:

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:)-