Need macro to autofill when there are blank cells

[Christy]

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

---
Regards,
Norman



"Christy" <Christy@discussions.microsoft.com> wrote in message
news:3D2E5CB4-13E3-4372-A2C6-A6CDCBBBA36D@microsoft.com...
>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?

[Tom Ogilvy]

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

--
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?