Repeat All Item Label in Excel 2007 -- Need Macro to do what Excel 2010 can do

[PaulLor89]

SampleDataLabel.xls

What I am looking to accomplish.
"Fill the Empty Cells Left by Pivot Table Row Fields"
http://datapigtechnologies.com/blog/...le-row-fields/

Long story short, company I work for is using Excel 2007 and won't be switching over to Excel 2010. Curious if there is a macro that will accomplish the same thing "Repeat All Item Label" in Excel 2010.

Any suggestion is welcome! Thanks!

[PaulLor89]

Bumping allowed?

[Andy Pope]

Select the range which contains the labels you want to replicate and then in the immediate window (ALT+F11 and then CTRL+G) use the following line.

selection.specialcells(xlBlanks).formular1c1="=R[-1]C"

It will not work on the pivot table, only a copied Copy-Paste Special-Values version.

[xlbiznes]

Hi,

Insert 3 columns. Now your data starts from column D.

Put this formula in the Cell A2 and copy the same till C141.

=IF(ISBLANK(D2),A1,D2)

[AB33]

Code

Sub UnmergeAndFillInTheBlanks()

    Dim cell As Range, LR As Long, rng As Range
    
    Const Colnum As String = "A:N"
        
    Application.ScreenUpdating = False
    Columns(Colnum).UnMerge
    LR = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    Set rng = Intersect(Rows(2 & ":" & LR), Columns(Colnum))
    On Error Resume Next
    For Each cell In rng.SpecialCells(xlCellTypeBlanks).Cells
        cell.Value = cell.Rows(1).Offset(-1).Value
    Next
    Application.ScreenUpdating = True
End Sub

[PaulLor89]

Thank you AB33!! It worked!