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.
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.
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.
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.
- The pivot table itself is on a worksheet called "Pivots". Columns A-C, Date in cell B2, main table headers in row 4.
- The date range is on a worksheet called "Paretos", cell refs below.
- 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: