Is there any reliable way to get the last used row of a sheet?

[OldManExcellor]

Is there any reliable way to get the last used row of a sheet?

By used row I mean a row that contains either a value or a formula.

[Olly]

This returns the last used row number of the active sheet:

Please Login or Register  to view this content.

[OldManExcellor]

This returns the last used row number of the active sheet:

Please Login or Register  to view this content.

Thank you for the attempt, but unfortunately that is not reliable.

In this example sheet the VBA statement returns 8 when it should return 4.
LastRow.xlsx

My questions was:
"Is there any reliable way to get the last used row of a sheet?
By used row I mean a row that contains either a value or a formula."

UsedRange also counts cells that have formatting and according to my experience cells that have had values at some point but don't anymore.

Any more suggestions?

[watersev]

option basing on the sample file:

Please Login or Register  to view this content.

[Olly]

Okay, then try:

Please Login or Register  to view this content.

[OldManExcellor]

I guess it depends on what a person would define as "reliable".

The suggested solutions give incorrect answers when filters are applied in Excel. You can test it on the sheet called suggestions 2 & 3 in the following file:
LastRow2.xlsx

Any more suggestions?

[Olly]

Edit: snipped. Still not right.

[Olly]

Okay, so you want to return the last row containing either a constant, or a formula (regardless of what the formula returns), regardless of which column it is in, regardless of whether it is visible or filtered, regardless of whether the file has been saved since last change... is that right?

Struggling to find a neater method than a loop:

Please Login or Register  to view this content.

[romperstomper]

I'd loop, but I'd loop rows, not cells:

Please Login or Register  to view this content.

[nilem]

or maybe something like

Please Login or Register  to view this content.

[OldManExcellor]

I'd loop, but I'd loop rows, not cells:

Please Login or Register  to view this content.

Looping from last row in used range and upwards is the best solution I have found as well.

But I've often heard that used range is unreliable. Will the answer always be correct?

[romperstomper]

Usedrange is only unreliable in about 5% of cases and less than 1% of cases where you use code to reset the usedrange. It's also only unreliable in that it:
1. Will overstate the range of used cells, not understate. (so looping back should be fine)
2. Will not take into account shapes on the sheet (including embedded charts)

For this purpose it should be fine.

[JOHN H. DAVIS]

FWIW:

Please Login or Register  to view this content.

[romperstomper]

I don't understand the logic of that. This part:

Please Login or Register  to view this content.

will always be 1 since you're just referring to one contiguous block of cells.

[kalak]

re: statements in post#12

would you like to confirm these are correct (using your code) for suggestion 1 in the downloaded file?

[romperstomper]

The statements are correct but there is an error in the code:

Please Login or Register  to view this content.

should read:

Please Login or Register  to view this content.