VBA - Finding the average of a range and ignoring zeroes - syntax question

**lordterrin** · 05-01-2014, 12:57 PM

The following formula accomplishes this in regular excel:

Please Login or Register  to view this content.

and I've tried to build it into a macro that I'm building by doing the following on a filtered range:

Please Login or Register  to view this content.

but it's not working.... I get the error "Run-time error 1004: Unable to get the CountIf property of the WorksheetFunction class"

The code works fine if I'm just trying to get the sum:

Please Login or Register  to view this content.

So I know the problem lies in my syntax of the second half of the formula.....

ideas?

**Winon** · 05-01-2014, 02:13 PM

Hello lordterrin,

I assume that line is nested within a With Statement. If so just try it like this.

Please Login or Register  to view this content.

Alternatively, you could try it this way.

Please Login or Register  to view this content.

Regards.

**lordterrin** · 05-01-2014, 03:53 PM

Hmmm - this did not work - since the .Countif counted ALL of the cells in the range (over 9000.) I solved this instead with the following:

Please Login or Register  to view this content.

Then I just used Worksheet.Function.Average as it no longer counted zeroes...

Thanks!

**Winon** · 05-02-2014, 03:53 AM

Hello lordterrin,

You could still simplify that, by changing your formula as shown in the attached sample Workbook.

Whether you filter the Column or not, even if there are zero's and/or blanks showing, it will return the correct average.

Anytime better than any Loop.

Kind Regards.

**lordterrin** · 05-02-2014, 11:19 AM

Thanks for this Winon! I agree that the loop is... not an ideal way of doing this, but the formula doesn't take into consideration filtered ranges. In your sheet, if I filter out all but two numbers, I still get the same average number, because it's taking into consideration EVERY cell - not every cell in the filtered range. Either way - your solution will definitely help me in another part of my sheet where I need to do exactly what you've shown me, so thanks

**Winon** · 05-02-2014, 12:56 PM

Hi lordterrin,

I really appreciate the feedback.

Yes, you are correct in your observation/s. What threw me here a bit, was the count only Cells >0.

Since you are manually selecting what to filter on, you might just as well always deselect the Zero and/or Blanks as well.

Having said that, and if you don't mind doing it, the revised attached sample Workbook will work exactly as you want.

Regards.