Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

This section presents two useful functions: LASTINCOLUMN, which returns the contents of the last nonempty cell in a column, and LASTINROW, which returns the contents of the last nonempty cell in a row. Chapter 15 presents array formulas for this task, but you may prefer to use a custom function.

Each of these functions accepts a range as its single argument. The range argument can be a column reference (for LASTINCOLUMN) or a row reference (for LASTINROW). If the supplied argument is not a complete column or row reference (such as 3:3 or D:D), the function uses the column or row of the upper-left cell in the range. For example, the following formula returns the contents of the last nonempty cell in column B:

=LASTINCOLUMN(B5)

The following formula returns the contents of the last nonempty cell in row 7:

=LASTINROW(C7:D9)

The LASTINCOLUMN Function

The following is the LASTINCOLUMN function:

Function LASTINCOLUMN(rng As Range) As Variant ' Returns the contents of the last non-empty cell in a column Dim LastCell As Range Application.Volatile With rng.Parent With .Cells(.Rows.Count, rng.Column) If Not IsEmpty(.Value) Then LASTINCOLUMN = .Value ElseIf IsEmpty(.End(xlUp).Value) Then LASTINCOLUMN = "" Else LASTINCOLUMN = .End(xlUp).Value End If End With End With End Function

Notice the references to the Parent of the range. This is done in order to make the function work with arguments that refer to a different worksheet or workbook.

The LASTINROW Function

The following is the LASTINROW function:

Function LASTINROW(rng As Range) As Variant ' Returns the contents of the last non-empty cell in a row Application.Volatile With rng.Parent With .Cells(rng.Row, .Columns.Count) If Not IsEmpty(.Value) Then LASTINROW = .Value ElseIf IsEmpty(.End(xlToLeft).Value) Then LASTINROW = "" Else LASTINROW = .End(xlToLeft).Value End If End With End With End Function