Function to find last value in a column

  • Thread starter Thread starter Andyjim
  • Start date Start date
A

Andyjim

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
 
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
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
 
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
 
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
 
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.
Click to expand...

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

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

Rick
 
Simply superb.

Swastik

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
Click to expand...
 
Gord,
I've been trying your solution to fetch what's in the last cell in column A and it works absolutely great, but you got me puzzled here.

What is the 2 in =LOOKUP(2, ?
What's the effect of the division 1/(A:A<>"")

Please enlighten me.

cheers,

Sybolt
 
Sybolt,

As I understand it, the division piece will return an array of values that includes one of two results:

1/TRUE = 1/1 = 1 and
1/FALSE = 1/0 = #DIV/0!

So any cell with contents will return a value of 1 and any empty cells will return an error. For example, an array of 5 cells in which the first 3 contain values and the last 2 are blank would look like this:

(1,1,1,#DIV/0!,#DIV/0!)

Since "2" is not present in the array (it can't be by definition), the LOOKUP function will return the last item in the array that is less than the LOOKUP value (in this case, the third item).

Ben
 
Sybolt,



As I understand it, the division piece will return an array of values that includes one of two results:



1/TRUE = 1/1 = 1 and

1/FALSE = 1/0 = #DIV/0!



So any cell with contents will return a value of 1 and any empty cells will return an error. For example, an array of 5 cells in which the first 3 contain values and the last 2 are blank would look like this:



(1,1,1,#DIV/0!,#DIV/0!)



Since "2" is not present in the array (it can't be by definition), the LOOKUP function will return the last item in the array that is less than the LOOKUP value (in this case, the third item).



Ben
Click to expand...



Sybolt,



As I understand it, the division piece will return an array of values that includes one of two results:



1/TRUE = 1/1 = 1 and

1/FALSE = 1/0 = #DIV/0!



So any cell with contents will return a value of 1 and any empty cells will return an error. For example, an array of 5 cells in which the first 3 contain values and the last 2 are blank would look like this:



(1,1,1,#DIV/0!,#DIV/0!)



Since "2" is not present in the array (it can't be by definition), the LOOKUP function will return the last item in the array that is less than the LOOKUP value (in this case, the third item).



Ben
Click to expand...

check this link you will get ans for sure.

http://www.xl-central.com/lookup-last-instance.html
 
Back
Top