Function to find last value in a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andyjim - 27 Jan 2008 20:01 GMT

Hi-

I submitted this post without entering anything on the subject line. So I
am re-entering this. I need to create a function that can be placed in a
cell that will display the last number in another column. Below are some
attempts, but no success. Thanks for your help!

Andy

Function LastValue(lastno As Variant)

Dim sysexpcol As Range
Set sysexpcol = Range(ad16, ad56)

Set lastno = sysexpcol(Cells.Value.xlDown)

End Function

Function lastvalueincolumn(foundcell As Variant)

With Worksheets("analysis").Range("ad:ad")
Set foundcell = .Cells.Find.Value.xlDown
End With
End Function

Reply to this Message

JLGWhiz - 27 Jan 2008 20:10 GMT

The best method for finding the last cell with data in a column on the active
sheet is:

lastRow = Cells(Rows.Count, "AD").End(xlUp).Row

You can substitute the column number without quotes where the "AD" appears.
It works either way. This does a bottoms up search of the column to find the
absolute last cell since the xlDown would stop if there is a blank cell
between the starting cell and the last cell. To set a variable to the range
for that cell:

myVar = Range("AD" & lastRow)

> Hi-
>
[quoted text clipped - 23 lines]
> End With
> End Function

Reply to this Message

Andyjim - 27 Jan 2008 20:37 GMT

Thanks for the help.

I not very experienced at creating functions as you can see.
I want to place this function if Cell d5 and have it display the value of
the last cell in column AD.

Function LastValue(lastno As Variant)

myvar = Range("AD" & lastrow)

Set lastno = Cells(Rows.Count, "AD").End(xlUp).Row
lastno = Range.Cells.Value

End Function

What am I doing wrong?

Thanks again for your help.

Andy

Reply to this Message

Rick Rothstein (MVP - VB) - 27 Jan 2008 20:55 GMT

> I not very experienced at creating functions as you can see.
> I want to place this function if Cell d5 and have it display the value of
> the last cell in column AD.

If that is all you want to do, just use this formula in D5...

=LOOKUP(2,1/(AD1:AD65535<>""),AD1:AD65535)

Rick

Reply to this Message

FSt1 - 27 Jan 2008 20:29 GMT

hi
you don't really need a function unless you just gotta have one
try something like this.

=OFFSET(A1,COUNTIF(A1:A500,">0")-1,0)

adjust cell references to suit your data.

regards
FSt1

> Hi-
>
[quoted text clipped - 23 lines]
> End With
> End Function

Reply to this Message

Gord Dibben - 27 Jan 2008 20:31 GMT

Why not use the functions Excel provides?

=LOOKUP(99^99,A:A) will fetch the last numeric in column A

=LOOKUP(REPT("z",255),A:A) fetch last text value in column A

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535) fetch wha is in last cell of column A

Gord Dibben MS Excel MVP

>Hi-
>
[quoted text clipped - 23 lines]
>End With
>End Function

Reply to this Message

Andyjim - 27 Jan 2008 21:36 GMT

Thanks to all for your answers. I'm sure they will work!

Reply to this Message