VBA for finding blank cells

0
votes

I have a piece of code wherein Data is lying in Sheet1.The code creates a pivot table in Sheet4 and I try to paste special the pivot in Sheet2.Till this it works fine. Now I try to find the blank cells in it and try to paste the value of upper cell. However it doesnt work! 

    Sheet2.Range("A1:D5").SpecialCells.Select
 Selection.FormulaR1C1 = "=R[-1]C"

I have just put in a sample range. I need this operation for entire pivot data which is lying in Sheet2. Any thoughts?

1
excelvba

1 Answers

1
votes

First Select the data portion of the Pivot Table (do not Select the labels) and then run:

Sub dural()
    If Selection(1) = "" Then Exit Sub
    On Error Resume Next
    Set r = Selection.Cells.SpecialCells(xlCellTypeBlanks)
    If r Is Nothing Then Exit Sub
    r.Value = Selection(1)
End Sub