Clear out old data in PivotTable dropdown lists.

TGKnIght

New Member
Joined
Jul 26, 2004
Messages
29
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.

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! :)
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
love to but this problem has been plaguing me to a lesser extent. usually my lists do empty themselves once the data is no longer there but it does remain somethings.
 
Upvote 0
Code:
Sub testIt()
    Dim aPF As PivotField, aPI As PivotItem
    With ActiveSheet.PivotTables("PivotTable1")
    On Error Resume Next
    For Each aPF In .PivotFields
        For Each aPI In aPF.PivotItems
            aPI.Delete
            Next aPI
        Next aPF
    On Error GoTo 0
    .PivotCache.Refresh
        End With
    End Sub
TGKnIght said:
Can anyone help me work on a programmatic way to clear these things out??
 
Upvote 0
You are the best, this seems to have worked perfectly. It clears out the old labels but does not reset which items I already have selected, nor reset the formatting. Thanks for the help :) I am posting my final code here.

Code:
Sub UpdatePivots()
Application.ScreenUpdating = False
'
' This function updates all pivottables in the workbook, recalculates and removes old data
'
    Dim ws As Worksheet
    Dim ip As Long
    
    For Each ws In ActiveWorkbook.Worksheets
        Application.StatusBar = "Updating " + ws.Name + "..."
        For ip = 1 To ws.PivotTables.count
            With ws.PivotTables(ip)
                On Error Resume Next
                For Each aPF In .PivotFields
                    For Each aPI In aPF.PivotItems
                        aPI.Delete
                        Next aPI
                    Next aPF
                On Error GoTo 0
                .PivotCache.Refresh
                .RefreshTable
            End With
        Next
        Calculate
    Next ws
Application.StatusBar = False
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top