capture last cell in column
 

is there a way to capture the value of the last populated cell in a
column?


--
bcamp1973
------------------------------------------------------------------------
bcamp1973's Profile: http://www.excelforum.com/member.php...o&userid=32268
View this thread: http://www.excelforum.com/showthread...hreadid=521186

capture last cell in column
 

Last Value in Column =INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A 1000<"")))) Last Numeric value
=LOOKUP(9.99999999999999E+307,A:A) Last Text value =MATCH(REPT("z",255),A:A)Sub GotoBottomOfCurrentColumn() 'Tom Ogilvy
2000-06-26 Cells(Rows.Count, ActiveCell.Column).End(xlUp).SelectEnd SubMore information
http://www.mvps.org/dmcritchie/excel/toolbars.htm http://www.mvps.org/dmcritchie/excel/lastcell.htm---HTH, David McRitchie,
Microsoft MVP - Excel [site changed Nov. 2001]My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page:
http://www.mvps.org/dmcritchie/excel/search.htm "bcamp1973" wrote
is there a way to capture the value of the last populated cell in a
column?

capture last cell in column
 

VBA?

Msgbox Cells(Rows.Count,"A").End(xlup).Value

or worksheet?

=MAX((IF(ISNUMBER(MATCH(REPT("z",255),D:D)),MAX(MA TCH(REPT("z",255),D:D)),0)
),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),M AX(MATCH(9.99999999999999E
+307,D:D)),0)))



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"bcamp1973" wrote
in message ...

is there a way to capture the value of the last populated cell in a
column?


--
bcamp1973
------------------------------------------------------------------------
bcamp1973's Profile:
http://www.excelforum.com/member.php...o&userid=32268
View this thread: http://www.excelforum.com/showthread...hreadid=521186

capture last cell in column
 

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("z",255),A:A))

Does that give you something to work with?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=521186

capture last cell in column
 

That's awesome, thank you!


--
bcamp1973
------------------------------------------------------------------------
bcamp1973's Profile: http://www.excelforum.com/member.php...o&userid=32268
View this thread: http://www.excelforum.com/showthread...hreadid=521186

capture last cell in column
 

You've already got your answer, but this was how my answer was supposed to look

Last Value in Column
=INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A 1000<""))))
Last Numeric value
=LOOKUP(9.99999999999999E+307,A:A)
Last Text value
=MATCH(REPT("z",255),A:A)

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

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

capture last cell in column
 

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: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=521186

capture last cell in column
 

Hi Ron,
Can you pls explain :
=INDEX(A:A,MATCH(10^99,A:A))
the function of 10^99

thank you
Syed


Ron Coderre Wrote:
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("z",255),A:A))

Does that give you something to work with?

Regards,
Ron


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=521186

capture last cell in column
 

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

"saziz" wrote in message
...

Hi Ron,
Can you pls explain :
=INDEX(A:A,MATCH(10^99,A:A))
the function of 10^99

thank you
Syed


Ron Coderre Wrote:
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("z",255),A:A))

Does that give you something to work with?

Regards,
Ron


--
saziz
------------------------------------------------------------------------
saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=521186

capture last cell in column
 

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: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=521186