Finding Last Cell value in row

[rfurman1161]

i have a simple spreadsheet that starts with the date on each row. Eac
spreadsheet can vary on the number of rows it has. I'm looking for a
way to find the value in the last date entered and use that data on
nother worksheet.

Any ideas?

Thanks

Ro

 

[Frank Kabel]

Hi Rob
if there're no blank rows in between you may use the following for
column A:
=OFFSET($A$1,COUNTA($A:$A)-1,0)

if you have blank rows in between tyr the following depending of the
type of values in your column:
1. If you have only text values in column A try
=INDEX(A:A,MATCH(REPT(CHAR(255),255,A:A))

2. If you have only numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E300,A:A))

3. If you have both (text and values)
=INDEX(A:A,MAX(MATCH(9.99999999999999E300,A:A),MATCH(REPT(CHAR(25
5),255),A:A)))

3.a. or an alternative for 3.: Use the following array function
(entered with CTRL+SHIFT+ENTER)
=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW(A1:A10000))))

 

[Bob Phillips]

LastValue = Worksheets("Sheet1").Cells(Rows.Count,"A").End(xlUp).Value

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[rfurman1161]

Did some searching and found this:

http://www.j-walk.com/ss/excel/tips/tip30.htm


Works very well

Copied text:

Determining the last non-empty cell in a column or row
This tip presents two useful VBA functions that can be used in
worksheet formulas. LASTINCOLUMN returns the contents of the last
non-empty cell in a column; LASTINROW returns the contents of the last
non-empty cell in a row. Each function accepts a range as its single
argument. The range argument can be a complete column (for
LASTINCOLUMN) or a complete row (for LASTINROW). If the supplied
argument is not a complete column or row, the function uses the column
or row of the upper left cell in the range. For example, the following
formula returns the last value in column B:

=LASTINCOLUMN(B5)
The formula below returns the last value in row 7:

=LASTINROW(C79)
You'll find that these functions are quite fast, since they only
examine the cells in the intersection of the specified column (or row)
and the worksheet's used range.

The LASTINCOLUMN function
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
The LASTINROW function
Function LASTINROW(rngInput As Range) As Variant
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Rows(1).EntireRow
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINROW = WorkRange(i).Value
Exit Function
End If
Next i
End Function


Rob