Here I am going to show you how we can get the last non blank row number, value, and cell address. Now lets consider our column with data as "column - A". So if we want to find out the last non blank row in column A we can use any of the below formulas.
if range has blank cells in between:
For both text and number
Formula 1: =MAX(IF(ISBLANK(A:A),0,ROW(A:A)))
Formula 2: =MAX((A:A<>"")*(ROW(A:A)))
Formula 3: =SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A))))
Formula 4: =LOOKUP(2,1/(A:A<>""),ROW(A:A))
Formula 5: =MATCH("*",A:A,-1) or MATCH("",A:A,-1) - For text only
Formula 6: =MATCH(0,A:A,-1) - For numbers only
if range is continuous without blanks:
Formula 7: =COUNTA(A:A)
To get last row value prefix "INDEX" formula with any formula mentioned above as shown in green font:
=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))
To get last row address prefix "ADDRESS" formula with any formula mentioned above as shown in green font:
=ADDRESS(MAX((A:A<>"")*(ROW(A:A))),1)
The "1" mentioned at last is the column number. Because our data is in 'A' column we have kept it as 1.
Thank You!