Last Used Cell in a Row

Find the reference of the last used cell in a row

By Neale Blackwood

One of the articles on our site allows you to find the cell reference of the last used cell in a column. You can see it below in the related articles.

One of our readers asked if there is a similar formula to find the last used cell in a row.

Well, it got us thinking and experimenting. The answer for row 1 is below.

In Excel 97-2003

=ADDRESS(ROW(1:1),MAX(ROW(1:255)*(TRANSPOSE(A1:IU1)<>””)))

In Excel 2007

=ADDRESS(ROW(1:1),MAX(ROW(1:16383)*(TRANSPOSE(A1:XFC1)<>””))) (see below)

articleid_129_lastusedcellinarow_1_400

TRANSPOSE is used because the rows numbers go down rather than across and you need to convert the column entries to do the same, so the array will work.