quickest way to find last "used" row in a 2D array? (repost)
"RB Smissaert" wrote in message
...
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
|