VBA Make a Merged Title by the number of populated cells in the next row down

[Dave01]

Hi,

I havnt got anything to share as Im not sure how to start this, what I am trying to do is produce a centered title across a pivot table. The amount of merged cells for the title will depend on how many of the below cells are populated.

so in the picture C5 is my title, but as the Pivot will expand and shrink based on what is populated then the the title in C5 needs to expand and shrink with it, basically trying to add a title above the pivot table.

Im thinking

activates C5
colour no fill
unmerge

Count rows C6 to end of populated rows ( so from company Code to Grand total) total 11 cells in the row
Merge C5 by 11 rows. to make the title accross the pivot table

Any ideas please ?

thanks

Dave

 

[AlphaFrog]

    Rows(5).Clear
    With Range("C5").Resize(1, ActiveSheet.PivotTables(1).TableRange1.Columns.Count)
        .MergeCells = True
        .HorizontalAlignment = xlHAlignCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .Interior.ColorIndex = 44
        .Value = "This is my header"
    End With

 

[Dave01]

sorry for the delay

perfect thank you

David