bvsramesh
02-07-2012, 09:26 AM
Hi Team,
I am using the below code to hide "Blank" Items in Pivot Table items in the First Column and the other Column i am trying to show only "Blanks".
but my problem is when i am running the below code it taking a long time and still i can see some wrong in the pivot.
please help me and provide me a correct solution and please correct my if there is anything wrong.
Sub Show Blanks_Not_in_Imatch()
Dim pt As PivotTable, pi As PivotItem
Dim xlCalc As XlCalculation
Sheets("Not Reconciled in IMatch").Select
Set pt = Sheet5.PivotTables("PivotTable2")
pt.ManualUpdate = True
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
On Error Resume Next
For Each pi In pt.PivotFields("IMATCH_ACCOUNT").PivotItems
pi.Visible = True
Next pi
On Error GoTo 0
pt.ManualUpdate = False
With Application
.Calculation = xlCalc
.ScreenUpdating = True
End With
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
On Error Resume Next
For Each pi In pt.PivotFields("IMATCH_ACCOUNT").PivotItems
pi.Visible = False
Next pi
On Error GoTo 0
pt.ManualUpdate = False
With Application
.Calculation = xlCalc
.ScreenUpdating = True
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("IMATCH_ACCOUNT")
.PivotItems("(blank)").Visible = True
End With
End Sub
Regards,
Surya
I am using the below code to hide "Blank" Items in Pivot Table items in the First Column and the other Column i am trying to show only "Blanks".
but my problem is when i am running the below code it taking a long time and still i can see some wrong in the pivot.
please help me and provide me a correct solution and please correct my if there is anything wrong.
Sub Show Blanks_Not_in_Imatch()
Dim pt As PivotTable, pi As PivotItem
Dim xlCalc As XlCalculation
Sheets("Not Reconciled in IMatch").Select
Set pt = Sheet5.PivotTables("PivotTable2")
pt.ManualUpdate = True
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
On Error Resume Next
For Each pi In pt.PivotFields("IMATCH_ACCOUNT").PivotItems
pi.Visible = True
Next pi
On Error GoTo 0
pt.ManualUpdate = False
With Application
.Calculation = xlCalc
.ScreenUpdating = True
End With
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
On Error Resume Next
For Each pi In pt.PivotFields("IMATCH_ACCOUNT").PivotItems
pi.Visible = False
Next pi
On Error GoTo 0
pt.ManualUpdate = False
With Application
.Calculation = xlCalc
.ScreenUpdating = True
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("IMATCH_ACCOUNT")
.PivotItems("(blank)").Visible = True
End With
End Sub
Regards,
Surya