VBA Pivot Filter - Date Range
excel pivot table filter date range
vba pivot table filter
pivot table date filter only shows month
change pivot table filter vba
excel pivot table filter latest date
vba filter pivot table based on date range
vba get pivot table filter values
Looking to filter a pivot table to be within a date range. The date filter is at the top of the 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
Sub FilterPivotDates() Application.DisplayAlerts = False Application.ScreenUpdating = False Application.EnableEvents = False Dim ws As Worksheet, ws2 As Worksheet, pt As PivotTable, pf As PivotField, PI As PivotItem Dim FromDate As Date, ToDate As Date Set ws = ThisWorkbook.Worksheets("Pivots") Set ws2 = ThisWorkbook.Worksheets("Paretos") FromDate = ws2.Range("B1").Value ToDate = ws2.Range("E1").Value pivno = 1 MCCol = 25 Set pt = ws.PivotTables("PivotTable" & pivno) Set pf = pt.PivotFields("Date") 'On Error Resume Next Do While pivno < 2 '25 Set pt = ws.PivotTables("PivotTable" & pivno) Set pf = pt.PivotFields("Date") pt.PivotFields("Date").ClearAllFilters With pf For Each PI In pf.PivotItems If PI.Value >= Format(FromDate, "M/D/YYYY") And PI.Value <= Format(ToDate, "M/D/YYYY") Then PI.Visible = True Else PI.Visible = False Next End With pivno = pivno + 1 Loop Application.DisplayAlerts = True Application.ScreenUpdating = True Application.EnableEvents = True End Sub
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.
Using Excel 2016
Excel stores Dates as numeric values, even though you might change the format of it from
"dd-mmm-yy" or to a monthly name like
"mmmm", it really doesn’t change the way Excel stored the value inside, which is Numeric.
For instance, take
01-Feb-2018, if you copy >> Paste Special (Values only) to the neighbor cell, you’ll get
In your case, the best way is to compare the numeric values of the dates.
In your code, replace your line:
If Pi.Value >= Format(FromDate, "M/D/YYYY") And Pi.Value <= Format(ToDate, "M/D/YYYY") Then ...
If Pi.Value >= CDbl(FromDate) And Pi.Value <= CDbl(ToDate) Then ...
You could optimize your
Do While loop a little:
Do While pivno < 2 '25 Set pt = ws.PivotTables("PivotTable" & pivno) Set pf = pt.PivotFields("Date") With pf .ClearAllFilters For Each pi In .PivotItems ' since you already used .ClearAllFilters, you don't need to use Visible = True, ' only hide the ones which are not within your desired dates range If Not (pi.Value >= CDbl(FromDate) And pi.Value <= CDbl(ToDate)) Then pi.Visible = False Next End With pivno = pivno + 1 Loop
How to filter pivot items with date range with Excel VBA, I found a solution to display all items of the last 12 months. I've used the excel numerical value of dates and used this to filter the pivot items. 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.
Ok it appears I've found a workaround.
The source data used to create the pivot needs to be raw Excel numbers, 43108 or whatever rather than the date.
When this is done using CDbl(FromDate) & such appears to work.
Just an attempt to clarify, the issue stems from the pivot item name (or caption, or value etc.), when it is a date, not being able to be formatted or set or processed to anything other than a US date format. Trying to match any data to it doesn't seem to work, only changing the raw data as above & converting any filter criteria within the code using CDbl seems to get me anywhere.
Filter a Pivot Table or Slicer for the Most Recent Date or Period, Filter For Recent Date Or Period In Pivot Table - VBA Macro.xlsm Checkout my free video series on getting started with Macros & VBA to Hello, New to using VBA and thankful that I came across this forum. I have a pivot table where I am trying to update the Date filter based upon a date range input by the user. I have tried several different variations and have not gotten it to work. Any help or insight would be greatly
PivotItems.Value property returns a String value. Format function also returns a String (Variant) value.
Therefore, when you perform a comparison of PI.Value vs FromDate / ToDate, it seems that your code performs a TEXT comparison, not DATE comparison. In text comparison, some dates are rightfully "out of range".
Reproduce the simple table in picture below (ensure that all fields are set into text format before entering values), and you'll see that it can reproduce the same "buggy" behaviour as you mentioned.
Solution is, probably, to somehow convert the values you compare, back into date format.
VBA Pivot Filter - Date Range, 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 If you want to filter for a date range, move the field to the Row or Column area instead. To select specific dates: Click the drop down arrow on date field. To show the check boxes, add a check mark to "Select Multiple Items". In the list of dates, add check marks to show dates, or remove check marks to hide dates.
How to Use Excel Pivot Table Date Range Filters, If you want to filter for a date range, move the field to the Row or Column area instead. To Duration: 2:38 Posted: Mar 21, 2020 1 . Please follow Step 1 -2 of above method to create a pivot table. 2 . In the PivotTable Fields pane, please drag and drop the Date field to the Filter section, 3 . Please click the arrow beside (All) , check Select Multiple Items option in the drop-down list,
Filter Pivot Table by Date Range, First let me say I know very little about VBA however I need to get something similar to the slicer effect for the non excel 2010 users. What I am Pivot Tables and VBA can be a little tricky initially. Hopefully this guide will serve as a good resource as you try to automate those extremely powerful Pivot Tables in your Excel spreadsheets. Enjoy! Create A Pivot Table. Sub CreatePivotTable () 'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet.
https://social.msdn.microsoft.com/Forums/office/en, Hi, I am trying to filter a pivot table using VBA. Specifically a date range. So I want a user to enter a date from and to on a excel sheet and the pivot will then They all have a date range filter. I want to insert vba code that will change the date filter for all the pivot tables on the sheet when I change a date in a single cell on the same sheet. I am using Excel 2007 on Windows XP. I am pretty new to pivot tables, as well as VBA, but I'm learning quickly. The pivot tables I am working with have been
- Thanks for that. This is one solution I tried before but I can't get it to work. The reason seems to be PI.Value can't be converted to a string, it is always something like 4/1/2018 for 1st Jan 2018. if i try, e.g. CDbl(pi.value) i don't even get a result, using a msgbox for example doesn't even show a msgbox. - Sorry had a comment fail here :)
- sadly no I have the same issue. I did take your advice on the loop, cheers
- @amcghee1it seems your "dates" are not valid dates. Copy >> Paste them, and see what values you get
- They are dates alright, UK regional formatted dates. The issue seems to be how the pivot cache handles dates. Source: dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems They mention there the issue was solved in Excel 2013 but i seem to be having that issue. I don't think the issue can be reproduced unless excel is set to use the UK date format from what I understand. I should also say I've tried PI.caption & PI.name to see if i can work with the values that way but running into the same issue.