Identifying last filled area ('row') of an array via VBA?
 

I have a large variant array (1 to 50000, 1 to 50). I'd like to set a
variable equal to the number of 'rows' of data that are actually in the
array, so I can use that in other parts of my code. lbound/ubound seems to
return the whole array size regardless of how much of the array contains
data.

When checking for the last used row in a worksheet I use:

Function Find_Last(sht As Worksheet)
Find_Last = sht.Cells.Find(What:="*", After:=sht.Range("A1"),
LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
End Function

Is there an equivalent for arrays (that avoids looping the entire array and
checking for blank values)?

Thanks!
Keith

Identifying last filled area ('row') of an array via VBA?
 

Did you Dim your variant array with those limits, for example...

Dim MyArray(1 To 50000, 1 To 50) As Variant

or did Dim it as a plain Variant and assign an array of those dimensions to
it? Did the values loaded into the array come from a range on the worksheet?
If so, it would seem you could go back to the worksheet and test it for the
last row when you assign the range to the array.

Rick


"ker_01" wrote in message
...
I have a large variant array (1 to 50000, 1 to 50). I'd like to set a
variable equal to the number of 'rows' of data that are actually in the
array, so I can use that in other parts of my code. lbound/ubound seems to
return the whole array size regardless of how much of the array contains
data.

When checking for the last used row in a worksheet I use:

Function Find_Last(sht As Worksheet)
Find_Last = sht.Cells.Find(What:="*", After:=sht.Range("A1"),
LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
End Function

Is there an equivalent for arrays (that avoids looping the entire array
and checking for blank values)?

Thanks!
Keith