quickest way to find last "used" row in a 2D array? (repost)

[ker_01]

I have a 2D variant array that is populated with a large worksheet range
(large enough to ensure that I won't miss any data from some variable length
reports).

The actual number of 'used' rows in the array may change over time as well,
as there are pieces of code that may add or remove records (rows).

Right now, I'm looping the entire array every time I need to compare data,
which is inefficient because sometimes only a fraction of the array is
actually populated with data. I do recursive looping, so it adds a lot of
processing time.

Is there a parameter I can check that would tell me the last 'used' row of
an array? UBound gives the size of the array regardless of whether it is
populated, so probably isn't the right direction for me to go.

One option would be to loop the array to find the last used row directly,
but I'm not sure that would save me much time over just looping the array to
compare data in the first place.

Many thanks,
Keith

 

[RB Smissaert]

Why don't you get the last used row from the range rather than from the
array?
Something like: MsgBox Range(Cells(1), Cells(10,
10)).SpecialCells(xlLastCell).Row
Or even better assign your array to the used range, so you can forget about
the last used
row in the array.
In any case you don't have to loop the whole array as when you loop from
back to front
you can do an exit when you find a non-empty element.

RBS

 

[ker_01]

Why don't you get the last used row from the range rather than from the
array?
Something like: MsgBox Range(Cells(1), Cells(10,
10)).SpecialCells(xlLastCell).Row

I can do that on the initial load, but the size of the used data area
changes throughout my procedures. I was hoping to avoid tracking the 'used'
size with separate variables, if for no other reason that having a separate
variable makes it harder to troubleshoot if I miss incrementing it
somewhere.

Or even better assign your array to the used range, so you can forget
about the last used
row in the array.

That works for the initial assignment, but forces redim preserves when I
need to add items. That might still be ok, I just try to avoid that overhead
when possible, again to avoid human errors in the code at different points
where it might need resizing

In any case you don't have to loop the whole array as when you loop from
back to front
you can do an exit when you find a non-empty element.

Yep, that's kind of what I ended up with- but I have to check multiple
fields, as not every field in every 'row' is used. I was hoping there might
have been the equivalent of a used range parameter so I could use just one
number for each loop without all the extra checking. It doesn't sound like
there is a built-in parameter, so what I've got (although clunky) may be as
good as it gets.

Thanks,
Keith

 

[ker_01]

Why don't you get the last used row from the range rather than from the
array?
Something like: MsgBox Range(Cells(1), Cells(10,
10)).SpecialCells(xlLastCell).Row

I can do that on the initial load, but the size of the used data area
changes throughout my procedures. I was hoping to avoid tracking the 'used'
size with separate variables, if for no other reason that having a separate
variable makes it harder to troubleshoot if I miss incrementing it
somewhere.

Or even better assign your array to the used range, so you can forget
about the last used
row in the array.

That works for the initial assignment, but forces redim preserves when I
need to add items. That might still be ok, I just try to avoid that overhead
when possible, again to avoid human errors in the code at different points
where it might need resizing

In any case you don't have to loop the whole array as when you loop from
back to front
you can do an exit when you find a non-empty element.

Yep, that's kind of what I ended up with- but I have to check multiple
fields, as not every field in every 'row' is used. I was hoping there might
have been the equivalent of a used range parameter so I could use just one
number for each loop without all the extra checking. It doesn't sound like
there is a built-in parameter, so what I've got (although clunky) may be as
good as it gets.

Thanks,
Keith