filling empty cells below with cell above

[dshafique]

Hey guys, So i have a pivot table, and i need to copy it to another page to run calculations on it and what not. when i copy it over, there are a lot of blank cells under the cells with content, since it was merged in a pivot table. I'm trying to create a macro which would automatically fill the empty cells below it with the cell value above it. How would I go about doing that?

here's an example table

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]some name[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name2[/TD]
[/TR]
[TR]
[TD]some other name[/TD]
[TD]name 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


here's how what I'm trying to get.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Some Name[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD]Some Name[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD]Some Name[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD]Some Name[/TD]
[TD]name 2[/TD]
[/TR]
[TR]
[TD]Some Other Name[/TD]
[TD]name 3[/TD]
[/TR]
[TR]
[TD]Some Other Name[/TD]
[TD]name 3[/TD]
[/TR]
[TR]
[TD]Some Other Name[/TD]
[TD]name 4[/TD]
[/TR]
[TR]
[TD]Some Other Name[/TD]
[TD]name 4[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

You can repeat the labels in the pivot table. Right click the field in question-Field Settings-Layout and Print then check Repeat item labels. Is that what you mean?

 

[dshafique]

So the table im messing with is not a pivot table. I created a copy of just the text from my pivot table, and set it as a range. I'm trying to get a macro to automatically repeat labels. on my table.

 

[MARK858]

Possibly (with the layout as per the below)...

Sub FillCell2()
    With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row + 1)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With

    With Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row + 1)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
End Sub

or

Sub FillCell2()
    Dim fCell As Range
    On Error Resume Next

    For Each fCell In Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row + 1). _
        SpecialCells(xlCellTypeBlanks).Areas
        fCell.Value = fCell(1).Offset(-1).Value
    Next
    
    On Error GoTo 0

    On Error Resume Next
    
    For Each fCell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row + 1). _
        SpecialCells(xlCellTypeBlanks).Areas
        fCell.Value = fCell(1).Offset(-1).Value
    Next

    On Error GoTo 0
End Sub

Excel 2010
	A	B
2	some name	name
3
4
5		Name2
6	some other name	name 3
7
8		name 4
9
Sheet4