locked
Adjust Column Width & Row heights inside Pivot Table RRS feed

  • Question

  • Hi : I have a workbook containing multiple pivot tables across multiple worksheets. I would like to adjust the row height + column width utilizing vba across these pivot tables. My following code below is only for adjusting the row height applies to a specific pivot table. Would it be possible to include the following requirements in the code as well?  Thanks you heaps for any assistance.

    1) include desired column width in the code.

    2) can be applied to all pivot tables in the work book.

    3) Centre alignment only for values column and apply custom number format in thousands

    4) apply text wrap across all the columns inside pivot tables

    Sub chRowHeightinPivot()
    Dim pvT As PivotTable
    Dim cRow As Range
    Set pvT = Worksheets("Sheet2").PivotTables("PivotTable1")

    Application.ScreenUpdating = False

    pvT.TableRange1.Rows.AutoFit

    For Each cRow In pvT.TableRange1.Rows
        cRow.RowHeight = cRow.RowHeight + 20 'adjust the number to fit your needs
    Next cRow

    Application.ScreenUpdating = True

    End Sub

    Sub chRowHeightinPivot()
    Dim pvT As PivotTable
    Dim cRow As Range
    Set pvT = Worksheets("Sheet2").PivotTables("PivotTable1")

    Application.ScreenUpdating = False

    pvT.TableRange1.Rows.AutoFit

    For Each cRow In pvT.TableRange1.Rows
        cRow.RowHeight = cRow.RowHeight + 20 'adjust the number to fit your needs
    Next cRow

    Application.ScreenUpdating = True

    End Sub

    Sub chRowHeightinPivot()
    Dim pvT As PivotTable
    Dim cRow As Range
    Set pvT = Worksheets("Sheet2").PivotTables("PivotTable1")

    Application.ScreenUpdating = False

    pvT.TableRange1.Rows.AutoFit

    For Each cRow In pvT.TableRange1.Rows
        cRow.RowHeight = cRow.RowHeight + 20 'adjust the number to fit your needs
    Next cRow

    Application.ScreenUpdating = True

    End Sub

    Sub chRowHeightinPivot()
    Dim pvT As PivotTable
    Dim cRow As Range
    Set pvT = Worksheets("Sheet2").PivotTables("PivotTable1")

    Application.ScreenUpdating = False

    pvT.TableRange1.Rows.AutoFit

    For Each cRow In pvT.TableRange1.Rows
        cRow.RowHeight = cRow.RowHeight + 20 'adjust the number to fit your needs
    Next cRow

    Application.ScreenUpdating = True

    End Sub
    • Moved by Dave PatrickMVP Saturday, November 28, 2020 11:24 PM looking for forum
    Saturday, November 28, 2020 11:01 PM

Answers