: I am working  on writing  a simple macro  that computes the
: average, but I am having trouble  figuring out how to count  the
: number  of values  so that I know what to divide by...

AVERAGE is already a built-in function  in Excel. I assume you
are doing this for an exercise in writing macros.


: This is what I have so far:
:
: Function Average(rngNumbers As Range)
:
: average  = 0
: sum  = 0
: NumValues = ActiveSheet.UsedRange.Rows.Count

Do more testing.

Create a new workbook. Add three small ranges separated by
empty cells. Extend one range to be longer than the other two. Go
to the VB Editor (Alt+F11). Open the Immediate Window (Ctrl+G).
Shrink the window size down enough to see the worksheet. Add the
following to the window and press Enter.

ActiveSheet.UsedRange.Select

Do you see how all those empty cells were selected? Once you
see a visual representation of the cells you are selecting, you
may write better code. When I am having problems with a function,
I often start looking at each line of code to test my assumptions.
Showing which cells are selected is very helpful.

While we could use this method to count the cells in a range,
but the range we are interested in is already in rngNumbers. It
would be better to count the cells in that range.

Let's write a test function to count the cells in a range.
I realize this seems like overkill, but down the line you can
write tests for much more complex solutions. Mistakes in some of
those complex solutions may cost people money. Lot's of money.

Add this function to the new spreadsheet we created above.
Add the following to a few cells.

Function TestCount(CountRange As Range, KnownCount as Integer)

Dim iCount As Integer
iCount = CountRange.Rows.Count

TestCount = "Count test failed. Count = " & iCount

If iCount = KnownCount Then TestCount = "Count test Succeeded."

End Function

=TestCount(E4:G6, 9)
=TestCount(E1:E6, 6)
=TestCount(E3:G3, 3)


Of course this doesn't help  us out since the count is still
wrong. A little more testing on other ranges reveals that
TestCount always returns the number of rows  in the range. Careful
examination shows that we are counting the wrong thing.

CountRange.Rows.Count


Since we are counting cells, let's try this.

CountRange.Cells.Count


Change the function and recalculate the worksheet (F9). All the
tests should succeed.

Function TestCount(CountRange As Range, KnownCount as Integer)

Dim iCount As Integer
iCount = CountRange.Cells.Count

TestCount = "Count test failed. Count = " & iCount

If iCount = KnownCount Then TestCount = "Count test Succeeded."

End Function


Further testing revealed that CountRange.Count works as well.
Next, you might write a function to test the average of a range of
cells. Good luck.

Can you use the following?

TestRange = Range("D6:D8")
AverageTest = Application.WorksheetFunction.Average(TestRange)

I'm not sure where you are having trouble  or why you want a different way of
counting values, but some comments on your code ...

Your For Each loop is looking at every cell in the range, yet your NumValues
is taken from a count  of just the rows. This will not do what you want if
the range is wider than one column. You should be able to use the Count
attribute against the range itself to get the actual number  of cells in the
range.

But you say "values". Do you want not to count empty cells? Look at
CountA.

You don't need a loop to sum  a range. There is a Sum function. There is
also an Average function.