Microsoft Office Tutorials and References

In Depth Information

**Some Useful Worksheet Functions**

InRange, “<=” & num1)

End If

End With

End Function

Note that this function uses Excel’s COUNTIFS function. In fact, the
CountBetween
function is

essentially a wrapper that can simplify your formulas.

COUNTIFS was introduced in Excel 2007. Therefore, this function won’t work with

previous versions of Excel.

Following is an example formula that uses the
CountBetween
function. The formula returns the

number of cells in A1:A100 that are greater than or equal to 10 and less than or equal to 20.

=CountBetween(A1:A100,10,20)

The function accepts the two numeric argument in either order. So, this formula is equivalent to

the previous formula:

=CountBetween(A1:A100,20,10)

Using this VBA function is simpler than entering the following (somewhat confusing) formula:

=COUNTIFS(A1:A100,”>=10”,A1:A100,”<=20”)

Determining the last non-empty 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 isn’t 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)