Last Row with data

[MWE]

Is there a native Excel function or formula that could determine the last row in a given column with any data in it. This is easily done in VBA with the Find Method, but is there something analagous that could be used (without writing a UDF) without VBA?

Thanks

[johnske]

Hi Mark,

xld has a discussion on that very issue here http://www.xldynamic.com/source/xld.LastValue.html

You could also consider converting into a UDF one of the many techniques given here http://www.vbaexpress.com/forum/arti...ticle&artid=53.

Regards,
John

[MWE]

Hi Mark,

xld has a discussion on that very issue here http://www.xldynamic.com/source/xld.LastValue.html

You could also consider converting into a UDF one of the many techniques given here http://www.vbaexpress.com/forum/arti...ticle&artid=53.

Regards,
John

Another interest eccentricity from vbax ... note that the displayed content of your reply and what I get when I click on QUOTE is different. In particular, the 2nd para (starting with "You could also ...") is missing from the displayed content (at least for me). EDIT: see below

xld's stuff is pretty interesting, but it does not yield the last populated row or first unpopulated row, etc. Rather it returns the last numerical value (if the col contains numbers) or the last text string (if the col contains text), etc.

re a UDF, I have lots of code that I could use for this (even more special cases than you list in your article). That is the easy way out.

EDIT: the first time I looked at your answer/reply, the content was

Hi Mark,

xld has a discussion on that issue here http://www.xldynamic.com/source/xld.LastValue.html

Regards,
John

If I irradiate my cat will that give it eighteen half-lives?

The major part of getting the right answer lies in asking the right question...
http://www.geocities.com/johnske100/triangle2.jpg
Made your code more readable, use VBA tags | Help those helping you by marking your thread solved when it is.

When I posted my reply and vbax came back, the 2nd paragraph was there. Perhaps you did a last minute edit?

[johnske]

...When I posted my reply and vbax came back, the 2nd paragraph was there. Perhaps you did a last minute edit?

Sorry, yes that was exactly what happened...I found the 1st URL and pasted it, posted, then found the 2nd and pasted it as an edit.

John

[Bob Phillips]

xld's stuff is pretty interesting, but it does not yield the last populated row or first unpopulated row, etc. Rather it returns the last numerical value (if the col contains numbers) or the last text string (if the col contains text), etc.

It does show you how to get the last value as the title of the paper says, but it is not beyond the whit of man to study what it says and work it out. For instance

=INDEX(A:A,MAX(IF(ISBLANK(A1:A65535),0,ROW(A1:A65535))))

returns the last value, but it does that by indexing into the data range using the calculated last row. SO the last row is got with

=MAX(IF(ISBLANK(A1:A65535),0,ROW(A1:A65535)))

as an array formula.

[Zack Barresse]

Another interest eccentricity from vbax ...

Don't think so bud. Looks like it was explained already though.

.. or first unpopulated row ..

Well that is altogether different now isn't it. ...

=MATCH(0,(IF(ISBLANK(A1:A20),0,ROW(A1:A20))),0)

Of course, confirm with CSE, where A1:A20 is your range of desire.