2

In the source sheet of my pivot table; contains two columns; one for the names and another for the scores. As the thread title suggests, I want to display all names and their corresponding scores of those with even number scores.

An alternative way to accomplish this is to create a third column and ran an IF formula to check which numbers are odd. And then include this third column to the pivot table and use it as the filter.

But is there a way to do the same without modifying the source?

user1737389
  • 53
  • 6

1 Answers1

4

You can loop through the pivot items and then divide them by 2 to check if they are even numbers or not and if they are not then simply hide them. See this example. Please amend it to suit your needs. Also, I have not done any error handling. I am sure you can take care of that...

CODE

Option Explicit

Sub HideOddNumbers()
    Dim ws As Worksheet
    Dim pvtItem As PivotItem
    Dim pvt As PivotTable
    Dim pvtFld As PivotField

    '~~> Change this to the respective sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    '~~> Change this to the respective pivot name
    Set pvt = ws.PivotTables("PivotTable1")

    '~~> This is the column
    Set pvtFld = pvt.PivotFields("Scores")

    With pvtFld
        .CurrentPage = "(All)"
        .EnableMultiplePageItems = True

        '~~> Loop through all items in the pivot and
        '~~> divide the values by 2 and check if they are even or not
        For Each pvtItem In pvtFld.PivotItems
            If Val(pvtItem.Value) Mod 2 <> 0 Then pvtItem.Visible = False
        Next
    End With
End Sub

SCREENSHOT

Before:

enter image description here

After:

enter image description here

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • I tried-out your code and plugged-in my on values; the code runs and finishes without errors but nothing changes on the pivot table. The code does loops through all odd numbers scores (as I've cheked using a MsgBox). ' Tried adding ThisWorkbook.RefreshlAll and even manually refreshing the table using the refresh button but those didn't help. – user1737389 Oct 23 '12 at 00:49
  • Not sure if this helps but, I'm triggering the code using a CommandButton – user1737389 Oct 23 '12 at 00:53
  • That's what Ive used. Yet my problem defined on my first comment still exists. – user1737389 Oct 23 '12 at 13:04
  • May I see your sample file? If yes then please upload it in wikisend.com and share the link here... – Siddharth Rout Oct 23 '12 at 16:50
  • I was actually about to upload the file already but, I re-checked the code and the pivot table and finally was able to figure out the issue. It appears that I just needed to add a filter for the 'Score'. I did not notice it before in the screenshot you provided, silly me :) Thank you Siddharth! – user1737389 Oct 23 '12 at 23:43