Find first and last non-blank (non-empty) cell, and First blank cell in a row or column

[niksirat2030]

I saw and tried different methods to find number of row or column, for First and Last non-blank (non-empty) cell in a row or column, and First blank cell in a row or column.

Codes that include .End(xlUp) or similar to it, for the case that for example, A3:A10 is a defined table, but A3 is empty from the top and A10 is empty from the bottom, it does not answer and does not check cells A3 and A10.

I also found the following function:

Please Login or Register  to view this content.

and use that in my code as

iRow4 = vba_last_row(ThisWorkbook.Sheets("DATA"), "O")

which gives:

Please Login or Register  to view this content.

What's the solution?

[Artik]

Please Login or Register  to view this content.

Artik

[niksirat2030]

Please Login or Register  to view this content.

Artik

I try

Please Login or Register  to view this content.

1- error 13: try mismatch
2- run-time error 1004: Method 'Range' of object '_Worksheet' failed.
3- not error , but wrong answer, for my data:

Attachment 794177

[CheeseSandwich]

Perhaps the below will help:

Please Login or Register  to view this content.

[niksirat2030]

Perhaps the below will help:

Please Login or Register  to view this content.

Can use this code as a Function like vba_last_row to use that any place of my code , for example

iRow4 = vba_last_row(ThisWorkbook.Sheets("DATA"), "O") for find last nonempty cell in column O

or

iRow4 = vba_last_row(ThisWorkbook.Sheets("DATA"), "A") for find last nonempty cell in column A?

without depend on a certain predefined Table in any sheet.

If yes, how can do that?

[CheeseSandwich]

Are you working with tables or just ranges of data?

I ask as (xlup) will return the bottom of a table even if the range within the table is empty. This is why i have referenced the table column to then find the last used cell within that column. If you reference the entire column where the table resides then it will only find the bottom of the table and not the last used row within the table.

How many tables do you have on each sheet?
What column in each table are you using to identify the last/ first cell?

[jindon]

niksirat2030 ,

Find method raise the runtime error when no cell found, I mean no data in the search range.
So, Change to

Please Login or Register  to view this content.

Now you can use it like

Please Login or Register  to view this content.

When no data, the function returs 0.