All,

This is some code which I belive Debra Dalgleish supplied for me some
time back.

I am wondering how I can manipulate it for the following situations.

Only one worksheet
Only one Pivottable
Only one Field

I have tried to alter it myself but have had no luck with the nested
With's and such.


The Code:

Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For i = 1 To 2
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
pt.RefreshTable
Next
Next
Next
End Sub

Untested!


Option Explicit
sub aaa()

Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

set ws = worksheets("sheet1")
set pt = ws.pivottables("pivottablenamehere")
set pf = pt.pivotfields("namehere")

On Error Resume Next
For i = 1 To 2
'For Each ws In ActiveWorkbook.Worksheets
' For Each pt In ws.PivotTables
' For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
' Next
pt.RefreshTable
' Next
'Next
Next
End Sub

wrote:

All,

This is some code which I belive Debra Dalgleish supplied for me some
time back.

I am wondering how I can manipulate it for the following situations.

Only one worksheet
Only one Pivottable
Only one Field

I have tried to alter it myself but have had no luck with the nested
With's and such.

The Code:

Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For i = 1 To 2
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
pt.RefreshTable
Next
Next
Next
End Sub

--

Dave Peterson