How to adjust column widths in a PivotTable automatically using VBA?

To automatically adjust column widths in a PivotTable using VBA, you can modify the `AutoFit` property of the columns in the PivotTable. Below is a step-by-step guide, along with a sample VBA code to help you achieve this:

Sub AutoFitPivotTableColumns()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim ptRange As Range
    
    ' Set the worksheet that contains the PivotTable
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to your sheet name

    ' Set the PivotTable
    Set pt = ws.PivotTables("PivotTable1") ' Change "PivotTable1" to your PivotTable name

    ' Set the range of the PivotTable
    Set ptRange = pt.TableRange2

    ' Autofit the columns of the PivotTable
    ptRange.Columns.AutoFit
End Sub

Enable Developer Tab: Ensure that the Developer tab is enabled in Excel to access the VBA editor.
Open VBA Editor:

Press `ALT` + `F11` to open the VBA editor.

Insert a Module:

In the VBA editor, right-click on any of the existing objects for your workbook.
Select `Insert` > `Module` to create a new module.

Write the VBA Code:

Copy and paste the VBA code below into the module window.

Customize the Code:

Replace `”Sheet1″` with the name of the worksheet containing your PivotTable.
Replace `”PivotTable1″` with the name of your PivotTable (you can find this under PivotTable Options).

Run the Macro:

Close the VBA editor.
Return to Excel and navigate to the `Developer` tab.
Click on `Macros`, select `AutoFitPivotTableColumns`, and click `Run`.

This macro will automatically adjust the column widths of the specified PivotTable to fit the contents of the cells. Ensure the PivotTable and sheet names are accurate to avoid any errors. Adjust the code further if you have more specific requirements or multiple PivotTables to handle.

facebooklinkedin