Pivot table - row height subtotal

[Hidden Dan]

Hello friends,

I'm rebuilding an extensive spreadsheet containing a lot of macro's. To make maintenance a lot easier I decided to create a pivot table. This works fine, but it doesn't look the way I like.

Main concerns are related to 'Subtotals'. They do not show well and for a static sheet I would change 'Row height' to create additional white space. But as a pivot table is dynamic, this doesn't work.

So my question is, is there a VBA script available that automatically changes row height from 15 to 30 once it notices 'Subtotal' ? There may be at least 4 or 5 'Subtotals' in the pivot table.

I already make use of an 'update pivot table' macro so it may integrated with this one.


Thanks, Dan

 

[mart37]

Sub FormatSubtotalRow()
Dim LastRow As Long
Dim i As Long
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = 2 To LastRow
        If LCase(Left(Range("A" & i), 8)) = "subtotal" Then Rows(i).RowHeight = 30
    Next i
End With
End Sub

 

[Hidden Dan]

Thanks for the code and my huble apologies for slow reacting. I was unexpected off last week.

I tried the code but was unable to get it working. My pivot table starts at C26 so 'Total' can be found at column C and any 'Subtotal' at columns D and E.

Could this be of any influence to the code ?


Dan

 

[mart37]

yes
"A" refers to column A
you have to change that
2 To LastRow means that the macro runs from row 2
you have to change that
If LCase(Left(Range("A" & i), 8)) = "subtotal" Then Rows(i).RowHeight = 30 looks only after one column
for more columns copy the line or use OR

 

[Hidden Dan]

Got it working, thanks.

Dan