Microsoft Office Tutorials and References
In Depth Information
Some Useful Worksheet Functions
The LastInColumn function follows:
Function LastInColumn(rng As Range)
‘ Returns the contents of the last non-empty 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)) Then
LastInColumn = “”
LastInColumn = .End(xlUp).Value
End If
End With
End With
End Function
This function is rather complicated, so here are a few points that may help you understand it:
h Application.Volatile causes the function to be executed whenever the sheet is
h Rows.Count returns the number of rows in the worksheet. I used the Count property,
rather than hard-coding the value, because not all worksheets have the same number of
h rng.Column returns the column number of the upper-left cell in the rng argument.
h Using rng.Parent causes the function to work properly even if the rng argument
refers to a different sheet or workbook.
h The End method (with the xlUp argument) is equivalent to activating the last cell in a
column, pressing End, and then pressing the up-arrow key.
h The IsEmpty function checks whether the cell is empty. If so, it returns an empty string.
Without this statement, an empty cell would be returned as 0 .
The LastInRow function follows. This function is very similar to the LastInColumn function.
Function LastInRow(rng As Range)
‘ Returns the contents of the last non-empty cell in a row
With rng.Parent
With .Cells(rng.Row, .Columns.Count)
If Not IsEmpty(.Value) Then
LastInRow = .Value
ElseIf IsEmpty(.End(xlToLeft)) Then
Search JabSto ::

Custom Search