capture last cell in column

is there a way to capture the value of the last populated cell in a 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

That's awesome, thank you!

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:
http://www.mvps.org/dmcritchie/excel/search.htm "bcamp1973" <[email protected]> wrote
> is there a way to capture the value of the last populated cell in a
> column?

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)

"bcamp1973" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

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

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?

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

thank you
Syed

Originally Posted by 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] 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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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