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

[111StepsAhead]

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.

[GeneralDisarray]

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!

[AB33]

"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.

[111StepsAhead]

I would get in the habit of NOT using the "Range(J & Rows.count)" kind of statement.-GeneralDisarray

Yes, I did this when I first started and it came back to haunt me. I also like the header method you mentioned and will put that to use in the future.

Thanks for your input.

[111StepsAhead]

Please Login or Register  to view this content.

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.

[AB33]

The code I posted works with any data format. It uses the "Find" function and would not bother about counts and columns. It does searches across rows.

[GeneralDisarray]

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

[111StepsAhead]

The code I posted works with any data format. It uses the "Find" function and would not bother about counts and columns. It does searches across rows.

Yes, your code is great except if the worksheet is empty. I suppose, after thinking about it for a few days, I could just have it know the only error is an empty worksheet and deal with it accordingly.

Please Login or Register  to view this content.

The reason for this exercise is I try to make code as robust as possible. Some times I have the user select a file to "process" and if an error is thrown the user will have to deal with it.

[AB33]

Please do not reply with quote, just reply will do.
Okay, I see your point, but you could add another line

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[111StepsAhead]

wont happen.JPG

This is what happens when you run that line of code on an empty worksheet.