Need macro to autofill when there are blank cells

[Guest]

I am pulling a standard report from SAP. I have inserted lines, which left
blank cells in column A. Column A is primary key for my pivot table.
Therefore, i need macro that will recognize any blank cells in column A then
autofill them using the first filled out cell above the blank cell.

I tried
For each cell in range ("a:a")
If cell.value="" then
cell.offset(-1,0).copy
cell.offset(1,0).paste
end if
next cell

but this did not work. any suggestions?

 

[Norman Jones]

Hi Christy,

TryL

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Dim rng2 As Range

Set SH = ActiveSheet '<<==== CHANGE

With SH
Set rng = Intersect(.Columns("A"), .UsedRange)
End With

On Error Resume Next
Set rng2 = rng.SpecialCells(xlBlanks)
On Error GoTo 0

If Not rng2 Is Nothing Then
rng2.FormulaR1C1 = "=R[-1]C"
rng.Value = rng.Value
End If
End Sub
'<<=============

 

[Guest]

Sub Replaceblanks()
Dim rng as Range, rng1 as Range
set rng = Range(cells(1,1),cells(rows.count,2).End(xlup).offset(0,-1))
set rng1 = rng.specialcells(xlBlanks)
rng1.formulaR1C1 = "=R[-1]C"
rng.Formula = rng.Value
End sub