Last Cell used on a Row

Find the reference of the last cell used 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.

Someone 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)<>””))) 

How to calculate the last cell used on a row

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.

How to Use this

There are lots of ways you could use this formula and the results it produces.

  • You may want to know the last cell used so you can calculate where to import next month’s data (e.g. in a VBA Macro).
  • Maybe you’d like to always read the right-hand figures into your report because these will always be the most recent.
  • Perhaps you’d like to chart the last data imported.  You could use this formula to always display the last column of figures, then point your chart at this.

… let us know how you’re using this!