I have a simple task but can not figure out an easy way to do this. How can I lookup the last (lowest down) numeric value in a column?
Last numeric value in a column
-
-
-
Quote
Originally posted by martinloe
I have a simple task but can not figure out an easy way to do this. How can I lookup the last (lowest down) numeric value in a column?Go to Insert/Name/Define
Name this BigNum and enter in the Refers To window
=9.99999999999999+307
Then use this formula for your range
=LOOKUP(BigNum,range)
-
To be more precise: I have a column of entries. New data is periodically added below the previous data in the same column. I would like a formula in another column to always display the last entry from that column. Any ideas?
-
Quote
Originally posted by martinloe
To be more precise: I have a column of entries. New data is periodically added below the previous data in the same column. I would like a formula in another column to always display the last entry from that column. Any ideas?=INDEX(A:A,MATCH(REPT("z",90),A:A))
This is for text data
-
I should add one more comment. There may sometimes be blank cells in between cells with data.
-
-
Here is the answer to my own question. Insert the following as a new module in VBA:
Function LASTINCOLUMN(rngInput As Range)
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINCOLUMN = WorkRange(i).Value
Exit Function
End If
Next i
End Function -
Hi there.
This will select the last used cell in a column, you can modify it to suit your code.
Hope it helps.
Sub LastCellInColumn()
Range("A65536").End(xlup).Select
End SubRegards
Ted -
via formula :
=OFFSET(A1,MATCH(9.9999999999999E307,A:A)-1,0,1,1)
this assumes your column is column A and that your numeric entries don't exceed Excel's limit and that you may have blank cells in the column
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!