There is a little quirk that exists in the UI that you might encounter when coding
PivotTables that bears a quick mention here. When Excel finds blank or null data in a range
of data used in a PivotTable, and that field is used in the summary section, it defaults the
summary field to ???Count of field name??? even though ???Sum of field name??? may be a more
appropriate selection.
4. Drag the Qty field back down to the Values list.
CHAPTER 6 n PIVOTTABLES 239
Figure 6-17 shows Excel displaying ???Count of field name??? when we want to sum.
Figure 6-17. Count of Qty is the default due to the blank data record.
5. To prevent blank data from displaying, we can use the NullString property of the
PivotTable object. In the VBE, add the following subroutine to the project:
Sub ZeroForBlanks()
ActiveSheet.PivotTables("PivotTable1").NullString = "0"
End Sub
6. From the Macros dialog box, run the subroutine. Figure 6-18 shows the result of running
the ZeroForBlanks macro.
7. To fix Excel??™s inaccurate guess that we wanted to count the number of Qty records in
the summary section of our PivotTable, we can use the Function property of the
PivotField object. Add the following subprocedure to the standard code module:
Sub ChangeSummaryFunction()
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Qty")
.Caption = "Sum of Qty"
.Function = xlSum
End With
End Sub
Once this code runs, the PivotTable will look like it did in Figure 6-16.
CHAPTER 6 n PIVOTTABLES 240
Figure 6-18.
Pages:
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234