• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to select latest 5 values from ‘Row Labels’ of Pivot Table

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,


I have a pivot table. There are multiple values in ‘Row Labels’.

In the ‘Value Filters’ of ‘Row Labels’ I need to select latest 5 values.

The value are in 11th Jan to 15th Jan 2016 format. There are multiple such values.

I need to select latest 5 values in this pivot. How do I do this. Can anyone please help me in this.

PS - Will be uploading sample file after some time. Apology.
 
Assuming it's valid date format and it's belongs to RowLabel Field, PivotItems...
Something like below:
Code: 
Sub pvItemFilter()
Dim pvTbl As PivotTable
Set pvTbl = ActiveWorksheet.PivotTables("PivotTable2")
pbTbl.ClearAllFilters
pvTbl.PivotFields("Dates").PivotFilters.Add Type:=xlDateBetween, _
    Value1:="xx/xx/xxxx", Velue2:="xx/xx/xxxx"
End Sub

You should be able to use variables for Value1 & 2. Don't have time to test it now, but I'll check later.

FYI - Tried using .Index on PivotItem in the past to filter with IF statement, but couldn't get .Index to work.
 
Hi @Chihiro thanks a lot for the help. Sorry I did not make it clear. My mistake. Values are in '11th Jan to 15th Jan 2016' general format. These are not dates.

Values reflecting in Pivot table will be something like '11th Jan to 15th Jan 2016' and '18th Jan to 22nd Jan 2016'.

Have a nice day ahead. :)
 
Hi,

Please find attached file for reference. One more doubt is, the file which I have created for reference does not have values from Pivot table in order.

I need values in below mentioned order.
25th Jan to 29th Jan 2016
1st Feb to 5th Feb 2016
8th Feb to 12th Feb 2016
15th Feb to 19th Feb 2016
22nd Feb to 26th Feb 2016

The original Pivot I have, have these values in order.
I know how to re-arrange these details by using 'Move Up' or 'Move Down' option manually but do not know how to use it with coding.

Can anyone please help me in this. Good night. :)
 

Attachments

  • Chandoo.xlsm.xls
    20 KB · Views: 4
Last edited:
Assuming each new Row Label is added to bottom of source table...

Code: 
Sub filterLast5()
Dim pvField As PivotField
Dim pvItem As PivotItem
Dim lRow As Integer

ActiveSheet.PivotTables("PivotTable1").ClearAllFilters
Set pvField = ActiveSheet.PivotTables("PivotTable1").PivotFields("Item")
lRow = ActiveSheet.Range("F" & Rows.Count).End(xlUp).Row

For Each pvItem In pvField.PivotItems
With ActiveSheet
For i = 1 To (lRow - 5)
    If pvItem.SourceName = .Range("F" & i).Value Then
            pvItem.Visible = False
    Else
    End If
Next
End With
Next pvItem
End Sub

Will see about sorting later.
 
You must log in or register to reply here.
Back
Top