Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Which function can I use to get the value of the last not empy cell in a row in Excel 2010?

share|improve this question

Try this for indpendance from the active sheet

Function LastCellInRow(rw As Range) As Variant
    LastCellInRow = rw.EntireRow.Cells(1, rw.Worksheet.Columns.Count).End(xlToLeft).Value
End Function
share|improve this answer

If you want a regular worksheet function, this might work... you do need to restrict the range, but this will provide the last number, last text string, or last anything. This example shows a column, but works the same for a row range:

Number: =LOOKUP(2, 1/ISNUMBER($A$1:$A$30), $A$1:$A$30)

Text: =LOOKUP(2, 1/ISTEXT($A$1:$A$30), $A$1:$A$30)

Anything: =LOOKUP(2, 1/($A$1:$A$30<>""), $A$1:$A$30)

enter image description here

share|improve this answer

Try this VBA function:

Function LastInRow(intRow As Integer) As Variant
    LastInRow = Cells(intRow, Columns.Count).End(xlToLeft).Value
End Function

Likewise, this function will return the value of the last non-empty cell in a column:

Function LastInColumn(strColumn As String) As Variant
    LastInColumn = Cells(Rows.Count, strColumn).End(xlUp).Value
End Function
share|improve this answer
These will return the last value on the active sheet only. – chris neilsen May 12 '12 at 0:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .