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!