Pivot Tables and Blanks

[EliseFreedman]

Hi there

I am trying to write a macro to get rid of all the blank rows in my pivot tables. I have several pivot tables on a single worksheet. I am using the following code

 With ActiveSheet.PivotTables("NumberCommencingPhysio").PivotFields( _
        "Referral Month Number")
                 .PivotItems("(blank)").Visible = False
End With

This code works fine if a blank row exists in the pivot table. However, if there is no blank row then the code crashes.

I have tried enclosing the .pivotItems line within an If statement but that didnt work either

 With ActiveSheet.PivotTables("NumberCommencingPhysio").PivotFields( _
        "Referral Month Number")
        If .PivotItems("(blank)").Visible = True
         .PivotItems("(blank)").Visible = False
       End if
End With

Does anyone have any ideas

 

[xlbo]

Use error handling:

'turn off error handling
on error resume next

With ActiveSheet.PivotTables("NumberCommencingPhysio").PivotFields( _
        "Referral Month Number")
                 .PivotItems("(blank)").Visible = False
End With
'turn it back on again
on error goto 0

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question