Adjusting row height in pivot tables

[emfc00]

Hi all,
I found the below solution, thanks to parmel, and have tried to alter it for my own needs but have failed.

I create massive pivot tables which others get to stare at all day. A common complaint is that the lack of spacing between text makes their eyes bleed. Is there a way to run the below macro for all rows in all pivot tables on a worksheet, or at least on all selected cells (as opposed to just the active cell)?

Sub CellSpacer()
    With ActiveCell
        .Rows.AutoFit
        .VerticalAlignment = xlCenter
        .WrapText = True
        .RowHeight = .RowHeight + 12
    End With
End Sub

Thanks!

-e

 

[igold]

Does this help you at all...

Sub PivRowHeight()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim p As PivotTable
    Set p = ws.PivotTables("PivotTable1")
    p.RowRange.RowHeight = 30
End Sub

 

[emfc00]

It does not, I'm getting an error. Maybe because I have more than one pivot table on the worksheet?

 

[igold]

What is the error and did you change the name of the Pivot Table to match your particulars...

 

[emfc00]

Bah! Yes, I forgot I deleted a few pivot tables so there was no PivotTable1. I updated it, and it works as designed.

The benefit of the first macro was that it could take cells of various sizes and just add to it. Some cells contain text, so it needs to be wrapped and space added. Does that make sense?

 

[igold]

I am happy this is working for the row height. I did not look, but those other attributes may be available to change via the same method. I will poke around now.

Thanks for the feedback. If I come up with something similar to your OP I will post again...

 

[igold]

Errant post.

 

[igold]

It would seem as if you have to do the body of the Pivot Table with DataBodyRange, so something like this should work.

Sub PivRowHeight()


    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim p As PivotTable
    Set p = ws.PivotTables("PivotTable1")
    With p.RowRange
        .RowHeight = 20
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    With p.DataBodyRange
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    
    
End Sub

Additionally, I can see "AutoFit" in both .ColumnRange and .DataBodyRange in intellisense, but it throws an error...

 

[emfc00]

Many thanks! I think we are getting closer. I changed it to the following and it seems to adjust most cells in the table but not all. I'm not getting any errors.

Sub PivRowHeight()


Dim ws As Worksheet: Set ws = ActiveSheet
Dim p As PivotTable
Set p = ws.PivotTables("PivotTable1")
With p.RowRange
.RowHeight = .RowHeight + 12
.VerticalAlignment = xlCenter
.WrapText = True
End With
With p.DataBodyRange
.VerticalAlignment = xlCenter
.WrapText = True
End With


End Sub

 

[igold]

You are welcome, I am glad we got close to what you wanted. There has to be a way to get AutoFit to work, I tried a couple of things but they all made the code puke.

Thanks for the feedback. If you happen to figure out AutoFit, please post it.