Is there a function in Visual Basic that returns the
number of entries in an array? I am writing a code that
loops through cells in excel, looks for a value -- if
there is a value it assigns that value to the next spot in
an array -- if the cell is blank the loop is broken. I
need to know how many values were found.

Thanks
Keri

This code may help you out.

HighNum = UBound(MyArray)

If your array is Base 0 (which is the default), then the
number of elements will be HighNum + 1. So if HighNum = 8,
then there are 9 elements. Some of those elements could be
blanks.

tod

-----Original Message-----
Is there a function in Visual Basic that returns the
number of entries in an array? I am writing a code that
loops through cells in excel, looks for a value -- if
there is a value it assigns that value to the next spot
in
an array -- if the cell is blank the loop is broken. I
need to know how many values were found.

Thanks
Keri

.

UBound(ary)-LBound(Ary)+1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Keri" wrote in message
...
Is there a function in Visual Basic that returns the
number of entries in an array? I am writing a code that
loops through cells in excel, looks for a value -- if
there is a value it assigns that value to the next spot in
an array -- if the cell is blank the loop is broken. I
need to know how many values were found.

Thanks
Keri

Hi Keri,

Keri wrote:
Is there a function in Visual Basic that returns the
number of entries in an array? I am writing a code that
loops through cells in excel, looks for a value -- if
there is a value it assigns that value to the next spot in
an array -- if the cell is blank the loop is broken. I
need to know how many values were found.

The others' replies should answer your question. But I have a question for
you - if you have looped through the cells, shouldn't you already know the
number of elements you found? If not, you could simply add in an Integer or
Long counter variable that increments each time you find an element.

I'm not sure what range you're looping through, but depending on what you're
doing, it may be faster to get all the values in your range at once. For
example, if you want to get the values from A1 down column A until you hit
an empty cell, you could do something like this:

Sub test()
Dim vData As Variant

With Worksheets("Sheet1")
vData = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Value
End With

MsgBox UBound(vData, 1)
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]