VBA Pivot Filter - Date Range

amcghee1

Board Regular
Joined
Oct 28, 2009
Messages
53
Hi folks.
Looking to filter a pivot table to be within a date range. The date filter is at the top of the pivot (I have an online picture example at the bottom of this post showing the way the date is presented in my pivot), with the main table having 3 columns. I have a picture with actual examples but can't upload here.


  • If I enter a date range of 1st Feb. 2018 - 1st March 2018 the filter works perfectly.
  • If I enter a date range of 1st Feb. 2018 - 28th Feb 2018 the filter misses out the 3rd Feb - 9th Feb, picking back up again for the remainder of the data from the 10th Feb.

Different date ranges produce variants of this behavior.


From my research online this type of filtering in VBA has a bug of some sort where the code reads the data in US date format, regardless of Excel settings & the data itself (hence the formatting code, without it causes a mismatch error). I've seen a couple of workarounds online such as using CLng but the method below is the closest I've got.


  1. The pivot table itself is on a worksheet called "Pivots". Columns A-C, Date in cell B2, main table headers in row 4.
  2. The date range is on a worksheet called "Paretos", cell refs below.
  3. The table I'm working on here is PivotTable1


Code:
[I]Sub FilterPivotDates()[/I]
 
[I]Application.DisplayAlerts = False[/I]
[I]Application.ScreenUpdating = False[/I]
[I]Application.EnableEvents = False[/I]
 
[I]Dim ws As Worksheet, ws2 As Worksheet, pt As PivotTable, pf As PivotField, PI As PivotItem[/I]
[I]Dim FromDate As Date, ToDate As Date[/I]
 
[I]FromDate = ThisWorkbook.Worksheets("Paretos").Range("B1").Value[/I]
[I]ToDate = ThisWorkbook.Worksheets("Paretos").Range("E1").Value[/I]
 
[I]pivno = 1[/I]
[I]MCCol = 25[/I]
 
[I]Set ws = ThisWorkbook.Worksheets("Pivots")[/I]
[I]Set ws2 = ThisWorkbook.Worksheets("Paretos")[/I]
 
[I]Do While pivno < 2[/I]
[I]    Set pt = ws.PivotTables("PivotTable" & pivno)[/I]
[I]    Set pf = pt.PivotFields("Date")[/I]
[I]    pt.PivotFields("Date").ClearAllFilters[/I]
[I]    With pf[/I]
[I]        For Each PI In pf.PivotItems[/I]
[I]            If PI.Value >= Format(FromDate, "M/D/YYYY") And PI.Value <= Format(ToDate, "M/D/YYYY") Then PI.Visible = True Else PI.Visible = False[/I]
[I]        Next[/I]
[I]    End With[/I]
 
[I]pivno = pivno + 1[/I]
 
[I]Loop[/I]
 
[I]Application.DisplayAlerts = True[/I]
[I]Application.ScreenUpdating = True[/I]
[I]Application.EnableEvents = True[/I]
 
[I]End Sub[/I]


Stepping through using msgbox commands it seems the missing dates are failing on one of the date checks, so the AND function removes the entry. I can't work out whats going on.



 
Last edited:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

amcghee1

Board Regular
Joined
Oct 28, 2009
Messages
53
Seems resolved finally.

For anyone else running into this the only way I could consistently get the correct fitlering was to set all my raw date data to straight Excel numbers (43103 or whatever) then within the VBA code, make sure any references to the cells containing the date criteria for filtering are also converted CDbl(FromDate = ThisWorkbook.Worksheets("Paretos").Range("B1").Value) for example.

Trying to set anything to do with the pivot item within VBA doesn't seem to work, only changing the raw data does.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,523
Messages
5,548,547
Members
410,848
Latest member
anuradhagrewal
Top