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

Application.Volatile

With rng.Parent

With .Cells(.Rows.Count, rng.Column)

If Not IsEmpty(.Value) Then

LastInColumn = .Value

ElseIf IsEmpty(.End(xlUp)) Then

LastInColumn = “”

Else

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

calculated.

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

rows.

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

Application.Volatile

With rng.Parent

With .Cells(rng.Row, .Columns.Count)

If Not IsEmpty(.Value) Then

LastInRow = .Value

ElseIf IsEmpty(.End(xlToLeft)) Then