Sub MaxDatePivot()
Dim pfiPivFldItem As PivotItem
Dim dtmDate As Date
With Worksheets("Sheet1").PivotTables(1)
.PivotCache.Refresh
.ClearAllFilters
With .RowRange
dtmDate = Evaluate("MAX(IF(ISNUMBER(" & .Address(0, 0) & ")," & .Address(0, 0) & ",))")
End With
For Each pfiPivFldItem In .PivotFields("End Date").PivotItems
If pfiPivFldItem.Value = "(blank)" Then
pfiPivFldItem.Visible = False
Else
pfiPivFldItem.Visible = (CDate(pfiPivFldItem.Value) = CLng(dtmDate))
End If
Next pfiPivFldItem
End With
End Sub