The recent TRIMRANGE function update to Excel makes it easier to determine the last used row in a column.

In the image below, the yellow cell has an IF function that returns a blank cell.

The formula for the last used row is.

=MAX(ROW(A.:.A))

This new range reference separator is shorthand for the new TRIMRANGE function. TRIMRANGE removes blank cells from the start and/or the end of a range.

You can now use the following characters between references to mimic TRIMRANGE.

:. trim the end of the range

.: trim the start of the range

or

.:. trim the start and end of the range

Trimming means removing blank / empty cells.

Trimming uses shorter ranges and hence is more efficient.

To return the first used row in a range use.

=MIN(ROW(A.:.A))

This new range referencing will make creating scalable solutions more efficient.