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 nonempty cell in a column; LASTINROW returns the contents of the last nonempty 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

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-code the value 65536, for forward compatibility. (A future version of Excel may contain more 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

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

The ISLIKE function is very simple (but also very useful). This function returns True if a text string matches a specified pattern.

This function, which follows, is remarkably simple. As you can see, the function is essentially a wrapper that lets you take advantage of VBA's powerful Like operator in your formulas.

Function ISLIKE(text As String, pattern As String) As Boolean ' Returns true if the first argument is like the second

ISLIKE = text Like pattern End Function

This ISLIKE function takes two arguments:

♦ text: A text string or a reference to a cell that contains a text string

♦ pattern: A string that contains wildcard characters according to the following list:

Character(s) in pattern Matches in text

End With

End With End Function

0 0

Post a comment