software

is there a way to capture the value of the last populated cell in a
column?--
bcamp1973
------------------------------------------------------------------------
bcamp1973's Profile: www.excelforum.com/member.php...oamp;userid=32268
View this thread: www.excelforum.com/showthread...hreadid=521186Last Value in Column =INDIRECT(quot;Aquot;amp;SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A 1000lt;gt;quot;quot;)))) Last Numeric value
=LOOKUP(9.99999999999999E 307,A:A) Last Text value =MATCH(REPT(quot;zquot;,255),A:A)Sub GotoBottomOfCurrentColumn() 'Tom Ogilvy
2000-06-26 Cells(Rows.Count, ActiveCell.Column).End(xlUp).SelectEnd SubMore information
www.mvps.org/dmcritchie/excel/toolbars.htm www.mvps.org/dmcritchie/excel/lastcell.htm---HTH, David McRitchie,
Microsoft MVP - Excel [site changed Nov. 2001]My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htmSearch Page:
www.mvps.org/dmcritchie/excel/search.htm quot;bcamp1973quot; gt; wrote
gt; is there a way to capture the value of the last populated cell in a
gt; column?

VBA?

Msgbox Cells(Rows.Count,quot;Aquot;).End(xlup).Value

or worksheet?

=MAX((IF(ISNUMBER(MATCH(REPT(quot;zquot;,255),D)),MAX(MA TCH(REPT(quot;zquot;,255),D)),0)
),(IF(ISNUMBER(MATCH(9.99999999999999E 307,D)),M AX(MATCH(9.99999999999999E
307,D)),0)))
--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;bcamp1973quot; gt; wrote
in message ...
gt;
gt; is there a way to capture the value of the last populated cell in a
gt; column?
gt;
gt;
gt; --
gt; bcamp1973
gt; ------------------------------------------------------------------------
gt; bcamp1973's Profile:
www.excelforum.com/member.php...oamp;userid=32268
gt; View this thread: www.excelforum.com/showthread...hreadid=521186
gt;

You have a few options:

1. The value of the last non-blank in Col_A:
=INDEX(A1:A65535,MATCH(2,1/(1-ISBLANK(A1:A65535))))
Note: Commit that array formula by holding down the [Ctrl][Shift] keys
and press [Enter].

2. The value of the last numeric value in Col_A:
=INDEX(A:A,MATCH(10^99,A:A))

3. The value of the last text value in Col_A:
=INDEX(A:A,MATCH(REPT(quot;zquot;,255),A:A))

Does that give you something to work with?

Regards,
Ron--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: www.excelforum.com/member.php...oamp;userid=21419
View this thread: www.excelforum.com/showthread...hreadid=521186
That's awesome, thank you!--
bcamp1973
------------------------------------------------------------------------
bcamp1973's Profile: www.excelforum.com/member.php...oamp;userid=32268
View this thread: www.excelforum.com/showthread...hreadid=521186You've already got your answer, but this was how my answer was supposed to look

Last Value in Column
=INDIRECT(quot;Aquot;amp;SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A 1000lt;gt;quot;quot;))))
Last Numeric value
=LOOKUP(9.99999999999999E 307,A:A)
Last Text value
=MATCH(REPT(quot;zquot;,255),A:A)

Sub GotoBottomOfCurrentColumn() 'Tom Ogilvy 2000-06-26
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
End Sub

More information
www.mvps.org/dmcritchie/excel/toolbars.htm
www.mvps.org/dmcritchie/excel/lastcell.htm

The array formula for picking up the last value is great, but how do you
find the last value in a column where there are cells below which hold
formulae (e.g. a look-up) but no values?

And how do you find the last value where there are fomulae, some of
which are returning zero?--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: www.excelforum.com/member.php...oamp;userid=25096
View this thread: www.excelforum.com/showthread...hreadid=521186
Hi Ron,
Can you pls explain :
=INDEX(A:A,MATCH(10^99,A:A))
the function of 10^99

thank you
SyedRon Coderre Wrote:
gt; You have a few options:
gt;
gt; 1. The value of the last non-blank in Col_A:
gt; =INDEX(A1:A65535,MATCH(2,1/(1-ISBLANK(A1:A65535))))
gt; Note: Commit that array formula by holding down the [Ctrl][Shift] keys
gt; and press [Enter].
gt;
gt; 2. The value of the last numeric value in Col_A:
gt; =INDEX(A:A,MATCH(10^99,A:A))
gt;
gt; 3. The value of the last text value in Col_A:
gt; =INDEX(A:A,MATCH(REPT(quot;zquot;,255),A:A))
gt;
gt; Does that give you something to work with?
gt;
gt; Regards,
gt; Ron--
saziz
------------------------------------------------------------------------
saziz's Profile: www.excelforum.com/member.php...foamp;userid=6350
View this thread: www.excelforum.com/showthread...hreadid=521186It just looks for a very big number, and finds the nearest value to it, the
last in the range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;sazizquot; gt; wrote in message
...
gt;
gt; Hi Ron,
gt; Can you pls explain :
gt; =INDEX(A:A,MATCH(10^99,A:A))
gt; the function of 10^99
gt;
gt; thank you
gt; Syed
gt;
gt;
gt; Ron Coderre Wrote:
gt; gt; You have a few options:
gt; gt;
gt; gt; 1. The value of the last non-blank in Col_A:
gt; gt; =INDEX(A1:A65535,MATCH(2,1/(1-ISBLANK(A1:A65535))))
gt; gt; Note: Commit that array formula by holding down the [Ctrl][Shift] keys
gt; gt; and press [Enter].
gt; gt;
gt; gt; 2. The value of the last numeric value in Col_A:
gt; gt; =INDEX(A:A,MATCH(10^99,A:A))
gt; gt;
gt; gt; 3. The value of the last text value in Col_A:
gt; gt; =INDEX(A:A,MATCH(REPT(quot;zquot;,255),A:A))
gt; gt;
gt; gt; Does that give you something to work with?
gt; gt;
gt; gt; Regards,
gt; gt; Ron
gt;
gt;
gt; --
gt; saziz
gt; ------------------------------------------------------------------------
gt; saziz's Profile:
www.excelforum.com/member.php...foamp;userid=6350
gt; View this thread: www.excelforum.com/showthread...hreadid=521186
gt;

Regarding: =INDEX(A:A,MATCH(10^99,A:A))

The 10^99 simply creates an impossibly large number to be used in the
worksheet (Excel can only handle values with up to 15 digits).

When the MATCH function does not find a match, it returns the position
of the last numeric value.

Side note:
As has been posted, a better way to return the last numeric value in a
column is:
=LOOKUP(10^10,H:H)

I hope that helps.

Regards,
Ron--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: www.excelforum.com/member.php...oamp;userid=21419
View this thread: www.excelforum.com/showthread...hreadid=521186