Filter values less than a number (with pivot)

[Christof]

Hi,

I have a macro and I'm trying to make it select only values greater than or equal to the value in a cell.

        Dim pt1 As PivotTable
        Dim Field4 As PivotField
        Dim NewCat4 As String
        Dim pi2 As PivotItem

        NewCat4 = Range("d3").Value

        Set pt1 = PivotTables("PivotTable1")
        Set Field4 = pt1.PivotFields("Ignore")

        pt1.AllowMultipleFilters = True

        For Each pi2 In Field4.PivotItems
            If Not ((pi2.Value >= NewCat4)) Then
                pi2.Visible = False
            End If
        Next

It's doing something, but it just seems to be filtering out random numbers...



Any ideas?

Cheers
Chris

 

[Chihiro]

Upload sample. But from the looks of it. The field isn't holding numeric, but string values. Hence seemingly random selection when you check for ">=".

I'd also recommend indicating your Excel/Office version when seeking help with Pivot Table codes. Few key features are dependent on version.

If unable to upload.
Try converting string to numeric.

Ex: CDbl(pi2.Value)>=CDbl(NewCat4)

 

[Chihiro]

Oh, and when working with PivotTable filter. Ensure that you clear filter prior to applying new filter. Otherwise, when no item matches criteria, code will error out.

 

[Christof]

Sample link here
https://drive.google.com/open?id=1l6fcbekuxB2BeIfZFhDCGIOg5bq0cGUN

Oh and I'm on 2016 version

 

[Chihiro]

Try something like below.

Sub Demo()
        Dim pt1 As PivotTable
        Dim Field4 As PivotField
        Dim NewCat4 As String
        Dim pi2 As PivotItem

        NewCat4 = Range("d3").Value

        Set pt1 = Sheets("CSQ Scores (All Questions)").PivotTables("PivotTable1")
        Set Field4 = pt1.PivotFields("Ignore")
        Field4.ClearAllFilters
        pt1.AllowMultipleFilters = True
        For Each pi2 In Field4.PivotItems
            If pi2.Value = "(blank)" Then
                pi2.Visible = False
            ElseIf Not ((CLng(pi2.Value) >= CLng(NewCat4))) Then
                pi2.Visible = False
            End If
        Next
End Sub

Though, I'd recommend using OLAP based pivot if you are able to.

 

[Christof]

That's awesome
Thank you!