Need macro to autofill when there are blank cells
 

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?

Need macro to autofill when there are blank cells
 

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
'<<=============

---
Regards,
Norman



"Christy" wrote in message
...
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?

Need macro to autofill when there are blank cells
 

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

--
Regards,
Tom Ogilvy



"Christy" wrote:

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?