CODEWITHSUNDEEP
vbaexcelexcel-2007excel-2003
Damian
Damian

Reputation: 4641

Filter Pivot in Excel

I have a problem with this filter if I set the regional language format to to English (United Kingdom), it works well if I use English (Unites States). My problem is that the filter does not make the selection of May 30 invisible, it fails in that call and goto the error section.

http://wikisend.com/download/138750/Pivottable.xls

is this a known bug? http://support.microsoft.com/default.aspx?scid=kb;en-us;114822&Product=xlw

It filters dates simular to post Filter pivottable in Excel 2003

Sub Filter()
    Dim PvtItem As PivotItem
    Dim ws As Worksheet

    On Error GoTo Whoa1

    Set ws = Sheets("pivot")

    '~~> Show All
    For Each PvtItem In ws.PivotTables("PivotTable1").PivotFields("Date").PivotItems
        PvtItem.Visible = True
    Next

    On Error GoTo Whoa2 '<~~ If no match found in Pivot

    '~~> Show Only the relevant
    For Each PvtItem In ws.PivotTables("PivotTable1").PivotFields("Date").PivotItems
        If Format(PvtItem.Value, "DD/MM/YYYY") <> Format(Range("today"), "DD/MM/YYYY") Then
            PvtItem.Visible = False
        End If
    Next

    Exit Sub
Whoa1:
    MsgBox Err.Description
    Exit Sub
Whoa2:
    '~~> Show All
    For Each PvtItem In ws.PivotTables("PivotTable1").PivotFields("Date").PivotItems
        PvtItem.Visible = True
    Next
End Sub

Upvotes: 0

Views: 1415

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

TRIED AND TESTED IN EXCEL 2003 (With UK Settings)

Right click on the Date Field as shown in the snapshot and click on Field Settings and then click on Number. Set the Date format as shown. Now try the same code.

enter image description here

Upvotes: 1

Related Questions