R
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
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