1

Using VBA I have been able to:

  • create the Pivot table
  • set the Row Fields for positions 1,2, and 3

Now I am trying to filter position 2 using VBA. This field contains dates except for "Not Yet Assembled." I only want to display the count of items that have not yet been assembled. The problem is that the macro will set some of the dates to FALSE but not all. I have checked the dates using the TYPE function and they are numbers.

I have searched most the day and used the following websites as references: Row count on the Filtered data

https://www.contextures.com/xlPivot03.html

https://www.mrexcel.com/board/threads/vba-hide-everything-but-1-item-in-a-pivot-table-field.586201/

Here is the code section that is not working:


With DSheet.PivotTables("pvtSK").PivotFields("ASSEMBLY DATE")
    .Orientation = xlRowField
    .Position = 2
End With
For Each Pi In PTable.PivotFields("ASSEMBLY DATE").PivotItems
    If Pi <> "NOT YET ASSEMBLED" Then Pi.Visible = False
Next Pi
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
RhettBrindle
  • 35
  • 3
  • Any error, or it just leaves some items unhidden? – Tim Williams Jun 28 '22 at 20:45
  • I have had issues with looping through PivotItems on PivotTables directly after creation. i had partial success by toggling `Application.ScreenUpdating` and calling for a `PivotTable.RefreshTable` before starting the loop. I don't know what the underlying problem is, and this solution was not 100% effective... unfortunately. – Toddleson Jun 28 '22 at 20:46
  • It leaves all dates and times unhidden. – RhettBrindle Jun 29 '22 at 14:31

1 Answers1

0

This should work:

With DSheet.PivotTables("pvtSK").PivotFields("ASSEMBLY DATE")
    .Orientation = xlRowField
    .Position = 2
    .ClearAllFilters  '<<< first remove any existing filtering
    For Each Pi In .PivotItems
        If Pi <> "NOT YET ASSEMBLED" Then Pi.Visible = False
    Next pi
End With

...assuming there is always a "NOT YET ASSEMBLED" entry in the Items list: if not then you'll get an error when it tries to hide the last item (at least one item needs to remain visible).

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • I tried this but still get the same issue - all dates and times remain. As a test I changed: If Pi <> "NOT YET ASSEMBLED" Then Pi.Visible = False to If Pi = "NOT YET ASSEMBLED" Then Pi.Visible = False. This will remove the NOT YET ASSEMBLED from position 2, but leave all the dates: – RhettBrindle Jun 29 '22 at 14:28