Microsoft Office Tutorials and References

In Depth Information

These functions are available on this topic's website, in a file named extended date

functions.xlsm. The website also contains a PDF file (extended date functions help.pdf)

that describes these functions. The functions are assigned to the Date & Time function

category.

The extended date functions don't make any adjustments for changes made to the cal-

endar in 1582. Consequently, working with dates prior to October 15, 1582, may not

yield correct results.

Returning the Last Nonempty Cell in a Column

or Row

This section presents two useful functions: LASTINCOLUMN, which returns the contents of the last nonempty

cell in a column, and LASTINROW, which returns the contents of the last nonempty cell in a row. Chapter 15

presents standard formulas for this task, but you may prefer to use a custom function.

This topic's website contains last nonempty cell.xlsm, a workbook that demonstrates

the functions presented in this section.

Each of these functions accepts a range as its single argument. The range argument can be a column reference

(for LASTINCOLUMN) or a row reference (for LASTINROW). If the supplied argument is not a complete

column or row reference (such as 3:3 or D:D), the function uses the column or row of the upper-left cell in the

range. For example, the following formula returns the contents of the last nonempty cell in column B:

=LASTINCOLUMN(B5)

The following formula returns the contents of the last nonempty cell in row 7:

=LASTINROW(C7:D9)

The LASTINCOLUMN function

The following is the LASTINCOLUMN function: