If it doesn't create a problem for any other areas of your code, perhaps use
vbNullString instead of vbEmpty. The underlying numeric value of vbEmpty is
zero. And average ignores text anyway - so it shouldn't create a problem
there.


"Geoff" wrote:

You are correct I am doing a Isnumeric test to remove non numeric
values. I then set the array element to vbempty. I tried clearing the
element but that did not work or rather I could not get the syntax to
work. vbempty removes the data but when I paste the array back into
the spreadsheet all the cleared cells contain zeros. I thought it was
beacuse I was using the following syntax for writing the array
back myRange.Value = myArray
myRange is the original data range contianing non-numeric values and
easily identified outliers such as values of 10^23. The data is form a
paper machine process control system that I need to massage.

I am fairly new to VBA and very new to arrays. If you would like to
see the whole code of the macro I will paste it into a message for you
to look at.

Thank you

On Jul 26, 2:04 am, JMB wrote:
How are you getting the data into the array in the first place? I entered
some data on a worksheet (leaving some empty cells), put those values in an
array and got the same answer in VBA as I do in excel (so the blanks in my
example are not being treated as zeros).

So far, I can only duplicate your problem if I use a type conversion
function, such as CLng, when the data gets loaded into the array, which will
convert the blank cells to 0's in the array. Is your data is being affected
by some explicit or implicit (as Jim said) type conversion?

You said you remove non-numeric data from the array. How is that done? If
you are using Isnumeric to test array elements prior to using some type
conversion function, include a test Len(arrayelement)0 as isnumeric of an
empty element will return True (because it can be coverted to 0).