View Full Version : [SOLVED:] 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
johnske
10-25-2005, 06:19 PM
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/articles.php?action=viewarticle&artid=53.
Regards,
John :)
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/articles.php?action=viewarticle&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 (http://showthread.php?t=3200) | 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
10-25-2005, 08:45 PM
...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
10-26-2005, 01:36 AM
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
10-26-2005, 11:24 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.