Specify AVERAGE function to ignore all blank range?

[Guest_imported]

I need to use the Average function on data that sometimes will wind up being all blanks (user hasn't entered that range yet). Apprently, this causes an error message.

Is there a way to specify the function so it ignores blanks and avoids the error? I know that ON ERROR RESUME NEXT will skip the error prone statement and is useful, but curious if there's some parameter that can be used with the function. Here's how I did it in the meantime...sure there's probably a more elegant streamlined method:

'COUNTING NUMBER OF NON-BLANK CELLS IN NEWRANGE
ActiveCell.Formula = "=CountA(" & newrange & &quot"

'MAKING SURE THE RESULT IS A VALUE, NOT FORMULA
ActiveCell.Value = ActiveCell.Value

'DOES FORMULA IF RANGE IS NOT ALL BLANKS
If ActiveCell.Value > 0 Then
ActiveCell.Formula = "=Average(" & newrange & &quot"
Else
ActiveCell.Value = ""
End If

'MAKES SURE RESULT IS VALUE, NOT FORMULA
ActiveCell.Value = ActiveCell.Value

 

[StevePB]

Hi sagain,

I have made the following minor changes to your code, and this seems to work whether the selection is populated or not. See what you think..

Sub testAvg()

'I used this section for testing
Dim newrange As Range
Range("A1:A4").Select
Set newrange = Selection
ActiveCell.Offset(0, 2).Select

'COUNTING NUMBER OF NON-BLANK CELLS IN NEWRANGE
' I added .Address to newrange in the following line
ActiveCell.Formula = "=CountA(" & newrange.Address & ")"

'MAKING SURE THE RESULT IS A VALUE, NOT FORMULA 
ActiveCell.Value = ActiveCell.Value 

'DOES FORMULA IF RANGE IS NOT ALL BLANKS
If ActiveCell.Value > 0 Then
' I added .Address to newrange in the following line
ActiveCell.Formula = "=Average(" & newrange.Address & ")"
Else
ActiveCell.Value = ""
End If

'MAKES SURE RESULT IS VALUE, NOT FORMULA 
ActiveCell.Value = ActiveCell.Value

End Sub

Regards, SteveB.