Pivot table not showing the selected items when filter added

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?

Can you post the workbook?

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:

Code:

---

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

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

Code:

---

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

---

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

Thanks so much!