Fill Blanks With Averages

[nicksoph]

Hi

I use Excel rarely and almost exclusively to generate data which is used to create Corel Draw images.
Searching on another site I found the following macro (written by Rick Rothstein) which seems to be close to what I am after. I think I understand everything except the important bit within the loop, which is not enough to be able to alter it to my needs, and wonder if someone would give an explanation of the line within the loop.

Sub FillBlanksWithAverages()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells(Rows.Count, "G").End(xlUp).Row
  For Each Ar In Range("G2:G" & LastRow).SpecialCells(xlBlanks).Areas
    Ar.Value = (Ar(1).Offset(-1) + Ar(Ar.Count).Offset(1)) / 2
  Next
End Sub

To try and explain what Im trying to achieve Im using an example of a grey scale image where each row generates a single integer between 0 and 255 representing a shade of grey but the intention is to triplicate the method to represent RGB.

In a spreadsheet with 2 columns 'A' & 'B': In Column A, I want to enter a value of between 0 and 255 in at least the top and bottom rows but may also enter values in any number of rows between them – in this example imagine we have entered the following four values –

10 in A1 (top row)
150 in A100,
75 in A900 and
20 in A1000 (bottom row).

In Column B I would like Excel to fill the intermediary values so that in this example they would gradually step up in value between B1 where it would show 10, to B100 where it would show 150 and then decreasing in value from B100 to B900 where it shows 75, and then decreasing from 75 to 20 between B900 to B1000.

I think the logic would go something like;

If this row has a value in Column A, use that value in Column B, otherwise

To calculate the value for column B in this row;

Use the A column value and row number of the first row ABOVE which has a value in column A and the A column value and row number of the first row BELOW which has a value in column A.

Take the value of ABOVE from the value of BELOW and divide by the number of rows between them, multiply that result by the number of rows between the current row and the row ABOVE, and then add the value in ABOVE.

I have already asked this question at Colour Gradients - Lookup next non-empty cell

[snb]

Since this is an Excel Forum I'd prefer a sample Excel file.

[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