How to hide or show field headers in a PivotTable using VBA?

To hide or show field headers in a PivotTable using VBA, you can manipulate the `DisplayFieldCaptions` property of the PivotTable object. This property allows you to control the visibility of field headers in your PivotTable.

Here’s how you can do it:

Hide Field Headers

To hide field headers in a PivotTable, set the `DisplayFieldCaptions` property to `False`.

Sub HideFieldHeaders()
    Dim ws As Worksheet
    Dim pt As PivotTable
    
    ' Reference the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Reference the PivotTable
    Set pt = ws.PivotTables("PivotTable1")
    
    ' Hide field headers
    pt.DisplayFieldCaptions = False
End Sub

Show Field Headers

To show field headers, set the `DisplayFieldCaptions` property to `True`.

Sub ShowFieldHeaders()
    Dim ws As Worksheet
    Dim pt As PivotTable
    
    ' Reference the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Reference the PivotTable
    Set pt = ws.PivotTables("PivotTable1")
    
    ' Show field headers
    pt.DisplayFieldCaptions = True
End Sub

Explanation:

Worksheet Reference: Change “Sheet1” to the name of the worksheet containing your PivotTable.
PivotTable Reference: Change “PivotTable1” to the name of your PivotTable.

Make sure you have the appropriate sheet and PivotTable names before running the macro. You can run these subroutines in the VBA editor to hide or show the field headers as needed.

facebooklinkedin