Last Row with data

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

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 :)

Quote:

---

Originally Posted by 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 :)

---

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?

Quote:

---

Originally Posted by MWE

...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 :)

Quote:

---

Originally Posted by MWE

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

Code:

---

=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

Code:

---

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

---

as an array formula.

Quote:

---

Originally Posted by MWE

Another interest eccentricity from vbax ...

---

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

Quote:

---

Originally Posted by MWE

.. or first unpopulated row ..

---

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

Code:

---

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

---

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