Exclude Blank Cells from Pivot Table Filter

[FarmGuy]

I am trying to select a date range in a pivot table whose ultimate function in the attached sample is to count the number of times that cell isn't blank. When using the code below, I get a type mismatch error. Suggestions on how to fix this code so that it excludes the cells that don't have a date from the filter?

Sub PageItemFilter()
Dim pvtF As PivotField
Dim pvtI As PivotItem

Set pvtF = Worksheets("PivotTables").PivotTables("PivotTable5").PivotFields("CDate")
For Each pvtI In pvtF.PivotItems
If DateValue(pvtI.Name) <= Range("E2").Value2 And DateValue(pvtI.Name) >= Range("E3").Value2 Then
pvtI.Visible = True
Else
pvtI.Visible = False
End If
Next pvtI
End Sub

 

[Chihiro]

You have (blank) in you PivotItem, which is giving this error as it can't be resolved in DateValue function.

Also, I think you have <= & >= sign reversed? I'm assuming that you want to check Cdate that's between E2 & E3.

Revised code:

Sub PageItemFilter()
Dim pvtF As PivotField
Dim pvtI As PivotItem


Set pvtF = Worksheets("PivotTables").PivotTables("PivotTable5").PivotFields("CDate")
For Each pvtI In pvtF.PivotItems
    If pvtI.Name <> "(blank)" Then
        If DateValue(pvtI.Name) >= Range("E2").Value2 And DateValue(pvtI.Name) <= Range("E3").Value2 Then
            pvtI.Visible = True
        Else
            pvtI.Visible = False
        End If
    End If
Next pvtI

End Sub

Edit: Or you can code for error trap.

 

[FarmGuy]

You have (blank) in you PivotItem, which is giving this error as it can't be resolved in DateValue function.

Also, I think you have <= & >= sign reversed? I'm assuming that you want to check Cdate that's between E2 & E3.

Revised code:

Sub PageItemFilter()
Dim pvtF As PivotField
Dim pvtI As PivotItem


Set pvtF = Worksheets("PivotTables").PivotTables("PivotTable5").PivotFields("CDate")
For Each pvtI In pvtF.PivotItems
    If pvtI.Name <> "(blank)" Then
        If DateValue(pvtI.Name) >= Range("E2").Value2 And DateValue(pvtI.Name) <= Range("E3").Value2 Then
            pvtI.Visible = True
        Else
            pvtI.Visible = False
        End If
    End If
Next pvtI

End Sub

Edit: Or you can code for error trap.

This still seems to select the "blank" checkbox in the filter. How do I get it to not select the blank in the filter

 

[Chihiro]

What is the issue?
If "(blank)", Count of Cdate is 0 anyways.

But you can modify the code like below.

Sub PageItemFilter()
Dim pvtF As PivotField
Dim pvtI As PivotItem


Set pvtF = Worksheets("PivotTables").PivotTables("PivotTable5").PivotFields("CDate")
For Each pvtI In pvtF.PivotItems
    If pvtI.Name <> "(blank)" Then
        If DateValue(pvtI.Name) >= Range("E2").Value2 And DateValue(pvtI.Name) <= Range("E3").Value2 Then
            pvtI.Visible = True
        Else
            pvtI.Visible = False
        End If
    Else
            pvtI.Visible = False
    End If
Next pvtI

End Sub