Returning the Last Nonempty Cell in a Column or Row

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.

On the CD 

The companion CD-ROM contains  last nonempty cell.xlsm, a workbook that demonstrates the functions presented in this section.

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 

Cross Ref 

In Chapter 15, I describe array formulas that return the last cell in a column or row.