PDA

View Full Version : Solved: Finding number of items in an Array



faye
11-30-2005, 08:30 PM
Hi all, is there a way to find the number of items in an array?

BlueCactus
11-30-2005, 10:03 PM
You can use LBound() and UBound() to find the lower and upper elements. That may or may not correspond to the number of items entered into the array, depending on how it was filled.

e.g.,:
Dim myArray(6)
firstIndex = LBound(myArray) ' will be zero in this case
lastIndex = UBound(myArray) ' will be six in this case

' Finding the first and last in the second dimension of a 2D array:
Dim myArray(0 to 8, 1 to 10)
firstIndex = LBound(myArray,2) ' will be one
lastIndex = Ubound(myArray,2) ' will be ten

johnske
11-30-2005, 11:05 PM
Or something along these lines...Sub WotAbout()
Dim MyArray(1 To 200), N As Long
For N = LBound(MyArray) To UBound(MyArray)
If IsEmpty(MyArray(N)) Then
MsgBox N - 1 & " items in array"
Exit For
End If
Next
End Sub

faye
11-30-2005, 11:09 PM
:clap: Thanks for the tips.

johnske
12-01-2005, 07:03 AM
Glad to help, try this also...Sub TryThis()
Dim MyArray(1 To 200)
'do your thing here
MsgBox WorksheetFunction.Count(MyArray)
End Sub