Microsoft Office Tutorials and References
In Depth Information
Function LASTINCOLUMN(rng As Range) As Variant
Returns the contents of the last nonempty cell in a column
Dim LastCell As Range
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 nonempty cell in a row
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
In Chapter 15, I describe array formulas that return the last cell in a column or row.
Search JabSto ::




Custom Search