locked
Macro for pivot table report filter, what if a value isn't present? RRS feed

  • Question

  • Assume a raw data table that is generated via an external process. The number of rows varies each time the process runs and the data content also changes.

    I have a macro that is building a pivot table and chart so that bit is working fine. The code is also created to set the Report Filter values so the pivot tables/charts show a defined subset of values.

    Most months, the data field that is used by the Report Filter will have the same range of values. In some months, we may be missing one or more of the values I'm asking the Filter to exclude. If this happens, the code errors out.

    I removed all raw data for 'RMA-TBD' and added an 'on error resume next' and this seems to allow the 'With' statement to set the 'RMA-Level 2 or 3' filter properly.

    Is there a better way to approach this problem?

    Thanks for the feedback and suggestions.

    Doug

        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Data Source + Level")
            .PivotItems("RMA-Not a complaint").Visible = False  'If the raw data doesn't have this value, then the code fails here
            .PivotItems("RMA-TBD").Visible = False                   'Ditto...
            .PivotItems("RMA-Level 2 or 3").Visible = False        'And again.
        End With

    Thursday, September 25, 2014 11:33 AM

Answers

  • Hello,

    If you put an on error resume next, in fact your code will fail also, but you tell it to ignore.

    one way is to use "on error resume next" for this, but keep your sub or function very small, only perform the part where you set the items to non-visible.

    another way is to check if your items are there. loop through your items, if you find them, set them to non visible.

    e.g.

    Dim colItems As New Collection, var As Variant
    Dim pi As PivotItem

    Call colItems.Add("RMA-Not a complaint")
    Call colItems.Add("RMA-TBD")
    Call colItems.Add("RMA-Level 2 or 3")

    For Each pi In ActiveSheet.PivotTables("PivotTable1").PivotFields("Data Source + Level").PivotItems
        For Each var In colItems
            If pi.Value = var Then
                pi.Visible = False
            End If
        Next
    Next


    I think both are OK, but document the dirty one ;)
    • Edited by Wouter Defour Thursday, September 25, 2014 12:19 PM just add some comments
    • Marked as answer by Dogubob Thursday, September 25, 2014 1:33 PM
    Thursday, September 25, 2014 12:18 PM

All replies

  • Hello,

    If you put an on error resume next, in fact your code will fail also, but you tell it to ignore.

    one way is to use "on error resume next" for this, but keep your sub or function very small, only perform the part where you set the items to non-visible.

    another way is to check if your items are there. loop through your items, if you find them, set them to non visible.

    e.g.

    Dim colItems As New Collection, var As Variant
    Dim pi As PivotItem

    Call colItems.Add("RMA-Not a complaint")
    Call colItems.Add("RMA-TBD")
    Call colItems.Add("RMA-Level 2 or 3")

    For Each pi In ActiveSheet.PivotTables("PivotTable1").PivotFields("Data Source + Level").PivotItems
        For Each var In colItems
            If pi.Value = var Then
                pi.Visible = False
            End If
        Next
    Next


    I think both are OK, but document the dirty one ;)
    • Edited by Wouter Defour Thursday, September 25, 2014 12:19 PM just add some comments
    • Marked as answer by Dogubob Thursday, September 25, 2014 1:33 PM
    Thursday, September 25, 2014 12:18 PM
  • Wouter,

    The 'on error resume next' is a blunt instrument that does the job.

    Your solution is much more elegant and easily extended if my list of values changes. I'll see if I can hook your code into mine.

    Thank you!

    Thursday, September 25, 2014 1:35 PM