Finding last row in a column

[daniel chen]

Column A contains entries of any formats including blanks.
My objective is to find the last blank row# in Column A, and post it in cell
D1.
Can you help me with a formula to do that? Thanks

 

[Myrna Larson]

I expect you haven't described your question correctly. The last blank row in
column A is most likely the last row on the worksheet, row 65536.

Do you mean the last *embedded* blank row?

 

[Domenic]

Hi Daniel,

Try the following array formula...

=MAX(IF(A1:A100="",ROW(A1:A100)))

entered using CONTROL+SHIFT+ENTER.

Hope this helps!

 

[daniel chen]

Hi, Thank for looking.
Let say (A1=7, A2= blank ,A3 = you, A4 = me) is given, the answer should
be 5.

 

[daniel chen]

Hi, Domenic
It didn't work. Maybe I should give the following example.
Let say (A1=7, A2= blank ,A3 = you, A4 = me) is given, the answer should
be 5.

 

[Biff]

Hi Daniel!

Try this. In cell D1 enter:

="A"&MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT
("z",255),A:A))+1

This will return A5. If you only want the number 5
remove "A"& from the formula. This also takes into account
cells that may formulas that reurn "".

Biff

 

[Frank Kabel]

Hi
try
=LOOKUP(2,1/(1-ISBLANK(A1:A1000)),ROW(A1:A1000))+1

 

[Domenic]

Make that...

=MAX(IF(1-ISBLANK(A1:A100),ROW(A1:A100)))+1

entered using CONTROL+SHIFT+ENTER. Although, I must say, I like Frank's
formula.

 

[daniel chen]

Thank you, Frank
That is exactly what I want.

Hi
try
=LOOKUP(2,1/(1-ISBLANK(A1:A1000)),ROW(A1:A1000))+1

 

[daniel chen]

Hi Domenic,
Frank Kabel's =LOOKUP(2,1/(1-ISBLANK(A1:A1000)),ROW(A1:A1000))+1 works
a lot better.
I appreciate your help very much.

 

[daniel chen]

Hi Biff,
It didn't work quite well.
Frank Kabel's =LOOKUP(2,1/(1-ISBLANK(A1:A1000)),ROW(A1:A1000))+1 do the
tricks.
Thank a lot.

 

[Guest]

Interesting formula, Frank! Your creation?

Hi
try
=LOOKUP(2,1/(1-ISBLANK(A1:A1000)),ROW(A1:A1000))+1

 

[daniel chen]

On 2nd look, this is ingenious, Frank.

Hi
try
=LOOKUP(2,1/(1-ISBLANK(A1:A1000)),ROW(A1:A1000))+1

 

[Frank Kabel]

Hi
to be honest the basic concept was 'borrowed' either from Harlan or
Aladin (not sure where I saw it first). I just adapted it to this
specific case and I just like the idea of this non-array-entered
approach

 

[icestationzbra]

hi,

i was wondering if there is a way frank's lookup formula would accept
the range dynamically.

i use this sheet wherein the rows are ever burgeoning... some days as
many as 500-1000 lines could be added.

currently i am using vba code to perform this functionality. it would
be of great utility if the same could be done using worksheet
functions.

thanks in advance,

mac.

 

[Frank Kabel]

Hi
you could always use:
=LOOKUP(2,1/(1-ISBLANK(A1:A60000)),ROW(A1:A16000))+1

or if for example the number of rows is stored in B1 (e.g. 10000) try
(not tested):
=LOOKUP(2,1/(1-ISBLANK(OFFSET(A1,0,0,B1))),ROW(OFFSET(A1,0,0,B1)))+1

 

[icestationzbra]

hi frank,

thanks for the reply.

i was trying to figure out if i could use offset in order to make th
formula dynamic. i was not able to, hence i posted my question here.

i used the formula you provided. if i enter the formula in B1 (when th
list is in column A), i get a circular reference error due to th
presence of B1 in the formula and the result is 0. if i enter th
formula in any other column (C, D, E...) i get #REF.

could you please help me with this, if its not too much o
inconvenience?

thanks,

mac

 

[icestationzbra]

hi frank,

i just tweaked your formula a wee bit, i am getting closer, but not
there yet.

column A has a,b,,c,d,,e,f,,g,h (from A1 to A11).

that is, A3, A6, A9 are blanks.

i used the following version of your formula in B1:

=LOOKUP(2,1/(1-
ISBLANK(OFFSET(A1,0,0,COUNTA($A:$A)))),ROW(OFFSET(A1,0,0,COUNTA($A:$A))))+1

i got the result as 9. the expected result is 12.

could you please help with this?

thanks in advance,

mac.

 

[Frank Kabel]

Hi
of course you get this result as COUNTA only returns the number of
filled cells. To be honest I don't know why you want to make i dynamic
(you won't gain a speed advantage - at least I dont think so). You why
not simply make the range large enough and use
=LOOKUP(2,1/(1-ISBLANK(A1:A60000)),ROW(A1:A16000))+1

With this you should have no problems

 

[icestationzbra]

i realise that using counta is not the right method for this situation,
it only returns the total number of cells that have data in them. seems
like hardcoding the cell number is the only way out.

mac.