Pivot Table with no "Blanks"

L

lt

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
 
G

Guest

On Error Resume Next

.PivotFields("Owner").PivotItems("(blank)").Visible = False
.PivotFields("Vendor").PivotItems("(blank)").Visible = False
.PivotFields("Website").PivotItems("(blank)").Visible = False
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Pivot Table Error 1
Pivot Table Error 1
syntax in VBA to create a pivot table 2
Pivot Table - How do I "Show All Data" of a particular field? 6
pivot table macro with a filtering variable 3
Pivot Table Filtering - Macro 3
Pivot table Macro 10
Pivot Table, Variable Inputs 1

Top