|
Pivot Table with no "Blanks"
Hi all,
I have the following code to rearrange a pivot table which by default includes the "Blank" values in the drop-down for all fields. How can I uncheck the "Blanks" option from the drop-down? Sub ViewbyOwner() ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Owner", "Category", "Expense Category", "Website", "FTEs") ActiveSheet.PivotTables("PivotTable1").PivotFields ("Owner").Subtotals = Array( _ True, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("Vendor").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("Website").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("Expense Category").Subtotals = Array( _ True, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("Category").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("FTEs").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("Owner").Fields = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) End Sub |
Pivot Table with no "Blanks"
On Error Resume Next
.PivotFields("Owner").PivotItems("(blank)").Visibl e = False .PivotFields("Vendor").PivotItems("(blank)").Visib le = False .PivotFields("Website").PivotItems("(blank)").Visi ble = False On Error GoTo 0 "lt" wrote: Hi all, I have the following code to rearrange a pivot table which by default includes the "Blank" values in the drop-down for all fields. How can I uncheck the "Blanks" option from the drop-down? Sub ViewbyOwner() ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Owner", "Category", "Expense Category", "Website", "FTEs") ActiveSheet.PivotTables("PivotTable1").PivotFields ("Owner").Subtotals = Array( _ True, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("Vendor").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("Website").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("Expense Category").Subtotals = Array( _ True, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("Category").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("FTEs").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable1").PivotFields ("Owner").Fields = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) End Sub |
| All times are GMT +1. The time now is 07:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com