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.



PivotFormat01.png
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top