Manipulate pivot tables in VBA

[Ronny]

Hi all,

I have a pivot table that I have to work with every day, and I was
hoping to automate a couple of things with VBA code. I have most of it
solved, except one thing. So I was hoping for some help here.
Is it possible to hide rows with the data 0?

So let's say that we have a list of customers (customers in the rows)
and revenue in the data section, and I want to "hide" the customers
with 0 revenue. There is something in the columns, so it needs to be
applied to the row total. There are two fields in the data section
(revenue and qty) and I want to hide the rows if it has 0 as revenue,
even if there will be a volume in the second data field.
And to make it even more complicated, it is according to the current
selection. There are selections in the page section, so in total the
revenue might be something else, but it is if the row total for the
current selection is 0 that I want to hide the item.


Product: "Selected product 1"

Month 1 Month 2 Total
Revenue Volume Revenue Volume Revenue Volume
Customer 1 2 000 11 3 000 17 5 000 28
Customer 2 1 000 4 2 000 8 3 000 12
Customer 3 0 1 0 0 0 1
Customer 4 0 0 0 0 0 0

In this case I want to not hide Customer 3 and 4 in the pivot table.

I have made a PT object (as pivot table) and two variables t and i (as
Integer)

With PT
With .PivotFields("Customer Name")
t = .PivotItems.Count
Debug.Print t 'About 8 000 names now

For i = 0 To t

'Hide item if row total for data "Revenue" is 0

Next i

End With


End With


Thanks in advance for any help.

Ronny

 

[Guest]

Hi Ronny,

It should be possible to catch changes to the pivottable with the Change
event on the worksheet that contains it. In the VBA editor you need to insert
a change event handler for the worksheet with the pivottable:

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "Yes, change was caught!!!" ' just to check that the change was
caught - remove later

' Insert your own code for first unhiding the whole range of the
pivottable
' and then going through it for hiding the rows with 0 in the desired
column

End Sub

The Target range given as the input parameter to event handler can be used
to check for what has been changed, if that is needed, e.g. the page field or
certain pivot fields.

Notice that this Sub has to reside in worksheet object (when you look in the
project explorer). If you put it in a module or under the workbook the event
handler will not execute.

I assume from your post that you know how to hide and unhide rows through VB
code, so I will not detail that here.


Hope that this helps


Best regards,

Frank M.