pivotfield.AutoShow(Type, Range, Count, Field)


Applies a filter to a pivot field.




The setting xlAutomatic applies the filter; xlManual removes the filter.


The setting xlTop shows the top Count of records; xlBottom shows the bottom Count of records.


The number of records to show.


The data field to use as the criterion of the filter.

These settings are equivalent to the Top 10 AutoShow options on the PivotTable Field Advanced Options dialog box, shown in Figure 13-20.

Setting advanced field options

Figure 13-20. Setting advanced field options

The following code shows the bottom ProductName item based on the data field (sales rank, lower is better):

Sub ShowBestSeller(  )
    Dim pt As PivotTable, pf As PivotField
    ' Uncomment next line to create pivot table.
    ' Get pivot table.
    Set pt = Worksheets("BookSales").PivotTables(1)
    ' Get pivot field.
    Set pf = pt.PivotFields("ProductName")
    ' Set autoshow.
    pf.AutoShow xlAutomatic, xlBottom, 1, pt.DataFields(1).name
End Sub

Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.