CODEWITHSUNDEEP
vbaexcel
Chetan Chimate
Chetan Chimate

Reputation: 81

Filter on Pivot Tables using Macro

I have put a filter on the Pivot Table. I have written a code that filters (removes) the entries with a value less than 0. I want to remove data entries with "0" and blanks too. Below is my code that filters entries that start with "-" sign. Please suggest how I can filter "0" and blanks.

Dim i As Integer
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Short Quantity")
    For i = 1 To .PivotItems.Count
           If Left(.PivotItems(i), Len("-")) = "-" Then
            .PivotItems(i).Visible = False
        Else
            .PivotItems(i).Visible = True
        End If

    Next i
End With

Upvotes: 0

Views: 597

Answers (2)

jeffreyweir
jeffreyweir

Reputation: 4824

So you only want positive numbers to show in the Pivot? If you put the 'Short Quantity' field into the pivot as a Values field as well as a row field, then you can put a Values filter on the field to suppress anything below zero, which also suppresses blanks:

Before:

enter image description here

Applying VALUES filter:

enter image description here

After:

enter image description here

Upvotes: 0

dwirony
dwirony

Reputation: 5450

Try:

Dim i As Integer
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Short Quantity")
    For i = 1 To .PivotItems.Count
        If Left(.PivotItems(i), Len("-")) = "-" Or _
                .PivotItems(i) = "(blank)" Or _
                .PivotItems(i) = 0 Then
            .PivotItems(i).Visible = False
        Else
            .PivotItems(i).Visible = True
        End If

    Next i
End With

Upvotes: 1

Related Questions