Pivot table not showing the selected items when filter added

[anne.gomes]

Hi,

This just suddenly occured yesterday and is still happening today. So my pivot table isn't showing its last value when I filter it to last 22 or 23 items. It only works when I filter it to last 24 items and 25 onwards. In all above situations it has all the selected items 'ticked', just doesn't show last value when i set value filters as anything less than 24.

Why is this?

It was working fine up until yesterday and I was able to show the last 22 items. This is the same problem I have even when I try adjusting filter on the pivot chart that's linked to pivot table.



Any help with this please?

[Bob Phillips]

Can you post the workbook?

[anne.gomes]

Hi xld,

I will post the workbook soon, but I decided to take another approach, I was wanting to only show the last 23 dates and so I wrote this:



Sub DeleteRows()
With Sheets("Sheet1")
LR = .Cells(Rows.Count, 1).End(xlUp).Row
For i = LR To 2 Step -1
If .Cells(i, 1).Value < DateAdd("d", -23, Date) Then
.Rows(i).EntireRow.Delete
End If
Next i
End With
End Sub


And it works but because of the amount of data and formulas it takes ages to complete. Is there a way to do it much faster?

Ive also tried finding and deleting in bulk, but because of the amount of data it still takes ages. I was thinking if there is a way to find the last 23rd date in the worksheet on col A and delete everything above it until it reaches "1 target".

How can this be done?

Thanks

Thank you

[Bob Phillips]

You could sort it first and find the row with the last date not wanted then delete rows 2:that_row in one go, or just use Autofilter


Public Sub DeleteOldDates()
Dim rng As Range
Dim lastrow As Long

Application.ScreenUpdating = False

With Worksheets("Sheet1")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = .Range("A2").Resize(lastrow)
.Range("A1").AutoFilter Field:=1, Criteria1:="<" & Format(Date - 23, .Range("A2").NumberFormat)
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
.Range("A1").AutoFilter
End With

Application.ScreenUpdating = True
End Sub

[anne.gomes]

Thank you xld, your code always works perfectly! I am going to try and understand what you did :)

Thanks so much!