Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.