Hi all. I am having an annoying issue with my Pivottables. In the dropdown boxes that AutoFilter data, I cannot seem to clear out old labels!
The big issue is that after running so many thousands of records through a table the dropdown list starts to get huge and ineffective.
I know that I could just as easily delete the pivottable and start over again, but this will defeat my purpose for using them in the first place.
I have found some code that might help, but I can't seem to get it to work just right. It resets my table, but doesn't do exactly what I'm looking for.
Please help!
The big issue is that after running so many thousands of records through a table the dropdown list starts to get huge and ineffective.
I know that I could just as easily delete the pivottable and start over again, but this will defeat my purpose for using them in the first place.
I have found some code that might help, but I can't seem to get it to work just right. It resets my table, but doesn't do exactly what I'm looking for.
Code:
Sub UpdatePivots()
Application.ScreenUpdating = False
'
' This function updates all pivottables in the workbook and recalculates.
'
Dim ws As Worksheet
Dim ip As Long
For Each ws In ActiveWorkbook.Worksheets
For ip = 1 To ws.PivotTables.count
For Each pvtfield In ws.PivotTables(ip).VisibleFields
sPvtField = pvtfield.Name
iFieldPos = pvtfield.Position
sFieldOrient = ws.PivotTables(ip).PivotFields(sPvtField).Orientation
On Error Resume Next
iFunction = ws.PivotTables(ip).PivotFields(sPvtField).Function
Err = 0
If iFunction <> 0 Then
GoTo Skip
End If
With ws.PivotTables(ip).PivotFields(sPvtField)
.Orientation = xlHidden
End With
ws.PivotTables(ip).PivotCache.Refresh
With ws.PivotTables(ip).PivotFields(sPvtField)
.Orientation = sFieldOrient
.Position = iFieldPos
End With
Skip:
Next pvtfield
ws.PivotTables(ip).RefreshTable
Next ip
Calculate
Next ws
Application.ScreenUpdating = True
End Sub
Please help!