Counting filtered rowfields in Pivot Tqable using VBA 1

[PhilH123]

Been through everything that I can find but can't work this one out, probably means it's simple!

I have a pivot table with 2 rowfields. I want to count the displayed items in rowfield2 when rowfield1 has a filter applied.

I can count all of the items in the rowfield2 using

Worksheets(1).PivotTables(1).PivotFields("Rowfield2").PivotItems.Count

I need the same but only for displayed items when Rowfield1 only has one item selected.

Thanks in advance for any help.
Phil.

 

[xlbo]

Untested:

ctr = 0
with Worksheets(1).PivotTables(1).PivotFields("Rowfield2")
For i = 1 to .PivotItems.Count
  if .PivotItems(i).visible = true then
     ctr = ctr + 1
  end if
next i
end with

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

 

[PhilH123]

Hi Geoff,

Thanks for the suggestion, but alas it just counts all of the values for Rowfield2.

I had tried using the Count property of Visibleitems previously too, this has the same results.

Kind regards, Phil.

 

[xlbo]

ok - try this

sub get_PivotItemCount()

With Worksheets(1).PivotTables(1).PivotFields("RowField1")

For i = 1 To .PivotItems.Count
  If .PivotItems(i).Visible = True Then
    MsgBox .PivotItems(i).DataRange.Rows.Count
  End If
Next

End With
end sub

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

 

[SkipVought]

This is a glaring shortcoming of PivotTables IMHO.

I'd skip the PT thing altogether and use a Query with the desired criteria to get this count.

This is basically what happens when you have multiple lists for a listbox or combo, each selection (criteria) narrowing the availble values in the subserviant list(s).

faq68-5829

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[xlbo]

The code I posted works tho.... ;-)

You don;t actually need the loop - that is just for if there is more than 1 item selected in "Rowfield1"

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

 

[PhilH123]

Thanks Geoff,

I have had to develop a different approach now to workaround other problems, but you helped me get a draft ready in time and avoid any hold ups.

Regards,
Phil.