Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi area Array using IRR function | Excel Worksheet Functions | |||
Assign a single variable to an Array filled with same data types | Excel Programming | |||
highest used area of an array ("uBound" of used area)? | Excel Programming | |||
Can a checkbox be placed 'within a row'? | Excel Programming | |||
Keeping an Array Filled and running macro from other workbooks? | Excel Programming |