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

some name	name
	Name2
some other name	name 3
	name 4

<tbody>
</tbody>

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

Some Name	name
Some Name	name
Some Name	name
Some Name	name 2
Some Other Name	name 3
Some Other Name	name 3
Some Other Name	name 4
Some Other Name	name 4

<tbody>
</tbody>

 

[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