locked
Using VBA to create filtered pivot tables but cannot add filter to metric with over 10000 unique records. RRS feed

  • Question

  • Good Day!

    I am trying to create a VBA macro that creates a filtered pivot table but the data column has over 10000 unique records. I only want to filter a couple items that I could do with somehow saying "Select anything beginning with e" but the only thing I have seen is code to show what NOT to select.

    Example

            .PivotItems("Flash Drive").Visible = False
            .PivotItems("Print To Scan").Visible = False
            .PivotItems("Remote").Visible = False
            .PivotItems("Fax").Visible = False
            .PivotItems("BCTS").Visible = False

    So my question is, is there VBA code that will create a filtered pivot only selecting items beginning with "e", instead of writing thousands of lines of code saying what NOT to select?

    Thank you.

    Bill in AZ

    Friday, January 20, 2017 6:08 PM

Answers

  • Hi,

    If there is no item beginning with e, I would get this runtime error. Because we are unable to make all items invisible.

    So you may need an error handler like:

    Sub Test()
    Set pvt = ActiveSheet.PivotTables("PivotTable1")
    Set fld = pvt.PivotFields("13")
    fld.ClearAllFilters
    For Each itm In fld.PivotItems
    If itm Like "e*" Then
    itm.Visible = True
    Else
    On Error GoTo handler
    itm.Visible = False
    End If
    Next itm
    handler:
    MsgBox "There is no item beginning with e"
    fld.ClearAllFilters
    End Sub

    If there are any other issues, please feel free to let me know.

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 24, 2017 2:55 AM

All replies

  • Hi,

    You could use For Each to loop all the pivotitems and then use Like Operator to check if it begins with "e".

    E.g.

    Set pvt = ActiveSheet.PivotTables("PivotTable1")
    Set fld = pvt.PivotFields("a")
    fld.ClearAllFilters
    For Each itm In fld.PivotItems
    If itm Like "e*" Then
    itm.Visible = True
    Else
    itm.Visible = False
    End If
    Next itm

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Chenchen Li Monday, January 23, 2017 10:29 AM
    Monday, January 23, 2017 3:14 AM
  • Thanks Celeste!

    I tried the following code

    Sub TESTY() Set pvt = ActiveSheet.PivotTables("Research1") Set fld = pvt.PivotFields("roisiteid") fld.ClearAllFilters For Each itm In fld.PivotItemsIf itm Like "e*"Then itm.Visible = True

    Else

    itm.Visible = False

    End If Next itm End Sub

    I get "Unable to set the Visible property of the Pivotitem Class" on the line "itm.Visible = False"



    Monday, January 23, 2017 11:28 PM
  • Hi,

    If there is no item beginning with e, I would get this runtime error. Because we are unable to make all items invisible.

    So you may need an error handler like:

    Sub Test()
    Set pvt = ActiveSheet.PivotTables("PivotTable1")
    Set fld = pvt.PivotFields("13")
    fld.ClearAllFilters
    For Each itm In fld.PivotItems
    If itm Like "e*" Then
    itm.Visible = True
    Else
    On Error GoTo handler
    itm.Visible = False
    End If
    Next itm
    handler:
    MsgBox "There is no item beginning with e"
    fld.ClearAllFilters
    End Sub

    If there are any other issues, please feel free to let me know.

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 24, 2017 2:55 AM
  • PERFECT!!

    Thank you so much for your assistance!

    Wednesday, January 25, 2017 5:21 PM