Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Hiding PivotItems using VBA (https://www.askmehelpdesk.com/showthread.php?t=329556)

  • Mar 15, 2009, 12:57 PM
    jakester
    Hiding PivotItems using VBA
    I have built a simple macro using VBA which hides certain dates from view in my pivot table. For example, I have date values like: 1/1/2007, 1/5/2007, 2/15/2007, 5/1/2008, 2/1/2009. I've been able create my macro to hide dates that I specify in the below code:

    Sub Macro1()
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Date")
    .PivotItems("1/2/2007").Visible = False
    .PivotItems("2/15/2007").Visible = False
    .PivotItems("5/1/2008").Visible = False

    End With
    End Sub

    My problem is that sometimes I have to refresh the data behind the pivot table and the dates can change but I will want to exclude certain dates from appearing in my pivot table results. For instance, I might want to exclude all dates prior to February 2007 or see only those dates that appear after January 2009. I can go into the pivot table and just de-select the dates but it's very tedious.

    Can I create code that will de-select all dates prior to a given period? For example, if I had dates throughout January and February 2009 and I only wanted to see February dates, can I make the code do something like hide all date values <2/1/2009? Is that possible without having to go into the pivottable and manually de-select all January dates?

    Hope this makes sense.
  • Mar 15, 2009, 09:28 PM
    JBeaucaire

    This page shows a technique for comparing NOW() to the .PivotItems and hiding the ones that result in "false" on the test.

    Hide Show Pivot Column Items Based on Date - Excel Help Best Practices Forums
    Code:

    Sub Macro1()
        '
        Dim pvtItem As PivotItem
        Dim strDate As String
       
        ' check  date  format - especially single digit days 01 or 1
       
        strDate = Format(Now(), "mm/dd/yyyy")
        With ActiveSheet. PivotTables("PivotTable1").PivotFields("Date")
            .ShowAllItems = True '
            For Each pvtItem In .PivotItems
                pvtItem.Visible = (pvtItem. Name = strDate)
            Next
        End With
    End Sub

    You could put that date to filter by in a CELL (A1? ) and adjust the formula to be greater than instead of equal:
    Code:

    Sub Macro1()
        '
        Dim pvtItem As PivotItem
        Dim strDate As String
       
        ' check  date  format - especially single digit days 01 or 1
       
        strDate = Format(Range("A1").Value, "m/d/yyyy")
        With ActiveSheet. PivotTables("PivotTable1").PivotFields("Date")
            .ShowAllItems = True '
            For Each pvtItem In .PivotItems
                pvtItem.Visible > (pvtItem. Name = strDate)
            Next
        End With
    End Sub

    This should give you enough to "fiddle with".
  • Mar 16, 2009, 10:17 AM
    jakester
    Quote:

    Originally Posted by JBeaucaire View Post
    This page shows a technique for comparing NOW() to a the .PivotItems and hiding the ones that result in "false" on the test.

    Hide Show Pivot Column Items Based on Date - Excel Help Best Practices Forums
    Code:

    Sub Macro1()
        '
        Dim pvtItem As PivotItem
        Dim strDate As String
       
        ' check  date  format - especially single digit days 01 or 1
       
        strDate = Format(Now(), "mm/dd/yyyy")
        With ActiveSheet. PivotTables("PivotTable1").PivotFields("Date")
            .ShowAllItems = True '
            For Each pvtItem In .PivotItems
                pvtItem.Visible = (pvtItem. Name = strDate)
            Next
        End With
    End Sub

    You could put that date to filter by in a CELL (A1 ??) and adjust the formula to be greater than instead of equal:
    Code:

    Sub Macro1()
        '
        Dim pvtItem As PivotItem
        Dim strDate As String
       
        ' check  date  format - especially single digit days 01 or 1
       
        strDate = Format(Range("A1").Value, "m/d/yyyy")
        With ActiveSheet. PivotTables("PivotTable1").PivotFields("Date")
            .ShowAllItems = True '
            For Each pvtItem In .PivotItems
                pvtItem.Visible > (pvtItem. Name = strDate)
            Next
        End With
    End Sub

    This should give you enough to "fiddle with".

    Thanks, guy... I've give you a "greenie" but I got to spread some of the love around. I think this will work so I will definitely "fiddle with" it.

  • All times are GMT -7. The time now is 01:45 AM.