capture last cell in column

D

David McRitchie

Last Value in Column =INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>"")))) 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:
 
B

Bob Phillips

VBA?

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

or worksheet?

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



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

Ron Coderre

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] key
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,
Ro
 
D

David McRitchie

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:A1000<>""))))
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
 
B

Brisbane Rob

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?
 
S

saziz

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

thank you
Syed


Ron said:
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
Click to expand...
 
B

Bob Phillips

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 said:
Hi Ron,
Can you pls explain :
=INDEX(A:A,MATCH(10^99,A:A))
the function of 10^99

thank you
Syed


Ron said:
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
Click to expand...
Click to expand...
 
R

Ron Coderre

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
 
Top