Determining the last nonempty cell in a column or row

In this section, I present two useful functions: LastInColumn returns the contents of the last non-empty cell in a column; LastInRow returns the contents of the last non-empty cell in a row. Each function accepts a range as its single argument. The range argument can be a complete column (for LastInColumn ) or a complete row (for LastInRow ). If the supplied argument is not a complete column or row, the function uses the column or row of the upper-left cell in the range. For example, the following formula returns the last value in column B:

=LastInColumn(B5)

The following formula returns the last value in row 7:

=LastInRow(C7:D9)

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:

■ Application.Volatile causes the function to be executed whenever the sheet is calculated.

■ Rows.Count returns the number of rows in the worksheet. I used this, rather than hard-coding the value, because not all worksheets have the same number of rows.

■ rng.Column returns the column number of the upper-left cell in the rng argument.

■ Using rng.Parent causes the function to work properly even if the rng argument refers to a different sheet or workbook.

■ 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.

■ 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 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 LastInRow = ""

Else

LastInRow = .End(xlToLeft).Value End If End With End With End Function

0 0

Post a comment