Statement Efficiency

[Cyberdude]

I have used the following statement to determine the bottom used cell in a column:

BotmRow = Cells(Rows.Count,MyCol).End(xlUp).Row

My concern is about the efficiency of using such a statement. Is Rows.Count time consuming? How do they perform End(xlUp)?? It seems like that would eat up a lot of cycles too.

[Zack Barresse]

No, it's very efficient actually. Rows.Count is a set number that Excel must only utilize, not compute per se. End(xlUp) is a 'computed' rendering of a range. And no, it does not take long or use erroneous resources. It is a very, very quick way of determining the last used row in a given column, in this case 'MyCol'.

Another added benefit (to which xld is trying to convert the world over to and, well, he has me convinced too!) is when/if MS comes out with another version of Excel (which I'm sure they will) and they happen to expand on the current 65,536 row limit, then you won't have to go back and change all your code which you did "Range("A65536").End(xlUp).Row". You will have covered your bases for future expansion. Which, IMHO, should be looked at just as much as backwards compatability. It's ALL about Preparation.

[Cyberdude]

Zack, sorry I didn't reply before now, but the editor wouldn't let me.
Thanks for a great explanation, and I feel much better about using "End(xlUp/Down)" now.
I think it would be neat to have a library of short recommendations between alternative coding techniques like you just wrote for me. They aren't long enough to be worth a full article, and they aren't the sort of thing you put in the KB, and they are easy to forget if you don't use them a lot. I started such a database for myself several years ago, and it has grown quite a bit as I pick up snippets here an there.
Thanx again.

[Bob Phillips]

I think it would be neat to have a library of short recommendations between alternative coding techniques like you just wrote for me. They aren't long enough to be worth a full article, and they aren't the sort of thing you put in the KB, and they are easy to forget if you don't use them a lot.

Not a KB perhaps, it isn't a single thread, but an article?