Fill Blanks With Averages

[p45cal]

See if this does anything for you:

Sub blah()
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("B1:B" & lr).Value = Range("A1:A" & lr).Value
For Each are In Range("B1:B" & lr).SpecialCells(xlCellTypeBlanks).Areas
  With are.Offset(-1).Resize(are.Rows.Count + 2)
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Trend:=True
  End With
  For Each cll In are.Cells
    cll.Value = Round(cll.Value)
  Next cll
Next are
End Sub

[nicksoph]

Hi P45cal

That worked perfectly, I really appreciate your helping.

It looks very elegant and introduces several new bits of Excel which I will attempt to understand.

Many thanks,

Nick