CODEWITHSUNDEEP
c#excelvba
Maro
Maro

Reputation: 2629

Repeat Row Labels On All Lines Of A Pivot Table Excel 2007

In Excel 2010 there is an option to repeat row Labels How can i do this in excel 2007 using Macro, Vba or C# ?

PS: i cannot do this manually(Copy paste) as it must be automated.

Upvotes: 0

Views: 2156

Answers (1)

rohrl77
rohrl77

Reputation: 3337

had the same problem just yesterday while converting a VBA programm to run on Excel 2007.

Here is my solution in words: Select the row or header labels that you want to fill, then use SpecialCells to select only blank cells. Put in R1C1 Formula to copy prior cell, then copy only the values. The on error resume next is there in case there are no blank cells.

Here is my answer in code:

    On Error Resume Next
    With .Range(.Cells(2, 1), .Cells(MaxRow, 1))
        .SpecialCells(xlCellTypeBlanks).Select
        Selection.FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    On Error GoTo 0

Hope this helps!

Upvotes: 1

Related Questions