CODEWITHSUNDEEP
vbaexcelpivot-table
Raji
Raji

Reputation: 53

Excel VBA to set Pivot Table filter with values between start and end value

I am trying to set filters in pivot table. The pivot table filter values range from 1 to 16. If start value=5 and end value=10, the pivot filters should be set to select the 5,6,7,8,9,10. Here is the code I have. It is selecting filter values from 1 to 10. I want the code to select 5 to 10. Please help 

Sub Macro1()    
    s = CStr(Range("J1").Value) 'start
    e = CStr(Range("K1").Value) 'end    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("WK")
        .ClearAllFilters
        .EnableMultiplePageItems = True
        For i = 1 To .PivotItems.Count
            MsgBox .PivotItems(i).Name
            If .PivotItems(i).Name < s And .PivotItems(i).Name > e Then
                .PivotItems(.PivotItems(i).Name).Visible = False
            End If
        Next i
    End With
End Sub

Upvotes: 0

Views: 7941

Answers (2)

Olly
Olly

Reputation: 7891

If PivotField "WK" is a Row or Column field, then you don't need to loop; you can simply apply a filter to the PivotField. It's also a good idea to qualify your range references with a worksheet.

Sub foo()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    With ws.PivotTables("PivotTable1").PivotFields("WK")
        .ClearAllFilters
        .PivotFilters.Add2 _
            Type:=xlCaptionIsBetween, _
            Value1:=ws.Range("J1").Value, _
            Value2:=ws.Range("K1").Value
    End With
End Sub

If PivotField "WK" is a Page field, then you do need to loop - but you are confusing your data types, and you have applied an AND operator where you mean OR. You can try this:

Sub FilterPageField()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim lPi As Long
    Dim lStart As Long
    Dim lEnd As Long

    lStart = CLng(ActiveSheet.Range("J1").Value)
    lEnd = CLng(ActiveSheet.Range("K1").Value)
    Set pt = Sheet1.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("WK")

    pf.ClearAllFilters
    For Each pi In pf.PivotItems
        lPi = CLng(pi.Caption)
        pi.Visible = lPi >= lStart And lPi <= lEnd
    Next pi
End Sub

Upvotes: 2

LJ01
LJ01

Reputation: 589

Im not sure if this is an answer exactly, it may be a solution...

What I usually do for this kind of problem is add a helper / filter column to the source data. Something to the effect of

if(and(source_data_col<j1,source_data_col>k1),true,false)

Then add that column to the pivot table filter.

Upvotes: 0

Related Questions