What is the ideal, 100% full proof, way to find the last row or last column?

I've been working with VBA for a few years now and over that time I've seen many ways to find the last row or last column in a spreadsheet. Any experts willing to put their reputation on the line and post the best way?

Some examples I've used before.

Please Login or Register  to view this content.

I've always like just using rows.count to find the bottom, the doing and xlup

Please Login or Register  to view this content.

For last column you have the similar columns.count

Please Login or Register  to view this content.

--edit--
The 1st and 3rd example you posted are similar, but you can get around using the "range(Adress)" and just use Cells(row#,col#)

Your second example fails because the find method will be null (is Nothing) and there for can't be referenced by .end(xlup).

I would get in the habit of NOT using the "Range(J & Rows.count)" kind of statement. Basically it works just fine, but if someone edits the sheet you will have to edit the macro.

Usually, it's handy to give the column headers a distinctive name, then used FIND to locate that column and pass in the column number. For example,

Please Login or Register  to view this content.

I like that last example because you can edit the sheet (and therefore move some columns around possibly) and not worry about having to track down hard-coded references (like your Range("J" & rows.count) example).

AVOID HARD-CODED REFERENCES!

"Any experts willing to put their reputation on the line and post the best way?"
I am not here for reputation and do not have any stake on it, but here are my ten cents

Please Login or Register  to view this content.

Originally Posted by 111StepsAhead

My issue with this code is I can only specify one row or one column to search. I'd like to have something that checks all used columns when looking for the last row and all used rows when looking for the last column.

hmm... that never occurred to me. I've never needed to do that, usually i care about specific fields (columns).


---

I was just trying to think of a reason you would need to do that, maybe need to add records where no column is guaranteed to be filled? Try the 'currentRegion' trick -- use this sometimes to fill blanks.
The current region allows for some blanks in a table, but not complete gaps (missing entire rows). Again, I'm not sure what you are doing, but this is worth knowing about -- there's nothing wrong with the other method by AB33.

To start with you need some kind of 'seed' cell or range. Like the upper left corner of a table or something.

Please Login or Register  to view this content.

Look up currentRegion, it's a bit of a long explanation. Usually i use it to fill in gaps in a table. To do that i would do this:

Please Login or Register  to view this content.

Again, if you are just looking for the last cell in a sheet for some reason try AB33's method, but if you would like to find the bottom of a rectangular range (and for some reason you can't guarantee there is a 'key' column that is always populated, try this