Fill blanks in a range with the average of that range

[Jo4x4]

Hi everybody,

I have a small range A1 to A10 which I fill with data. However, sometimes there may be two or three of the cells that has no value. I would like to fill these empty cells with the average of the cells that did have values.

Thanks
Jo

Win XP, Office 2010

 

[Warship]

Sub FillBlankAverage()
    Dim x As Integer
    Dim cnt As Single
    Dim sum As Single
    Dim Avg As Single
    For x = 1 To 10
        If Cells(x, 1) <> "" Then
            cnt = cnt + 1
            sum = sum + Cells(x, 1)
        End If
    Next
    Avg = sum / cnt
    For x = 1 To 10
        If Cells(x, 1) = "" Then Cells(x, 1) = Avg
    Next
End Sub

 

[Peter_SSs]

This non-looping method should be more efficient, though if only 10 cells it wouldn't matter.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> FillAvg()<br>    <SPAN style="color:#00007F">Dim</SPAN> Avg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> myRng <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A1:A10" <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    Avg = WorksheetFunction.Average(Range(myRng))<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    Range(myRng).SpecialCells(xlCellTypeBlanks).Value = Avg<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Jo4x4]

Hi Peter,
Thank you, but if cell A9 and A10 does not have values, it does not fill them, it only fills the other blanks?

Jo

 

[Peter_SSs]

Hi Peter,
Thank you, but if cell A9 and A10 does not have values, it does not fill them, it only fills the other blanks?

Jo

It does depend on what else is, or could be, in your sheet but you are correct there are circumstances that would trigger that behaviour. If you wanted to pursue this method for your sheet, I'd want to know a bit more about the sheet and what is happening on it. For example, is there ever anything in A11?

To see the effect, put something in your sheet anywhere below row 10 (eg F15) and run the code again.

 

[Jo4x4]

Thanks Peter, that worked a charm!
Thanks also to Warship.

Jo