How to find the last used row in a spreadsheet without using VB

I frequently copy spreadsheets between Microsoft Excel and Open Office Calc and I sometimes have a need to find the last row being used in a spreadsheet.  But since Open Office does not support Visual Basic for Applications (VBA), I needed a formulaic approach.

Here is the formula: {=MAX(NOT(ISBLANK($A$1:$A$65536))*ROW($A$1:$A$65536))}

This is an array formula, meaning, it will iterate over a range automatically and evaluate the formula for each cell in the range.  (NOTE: Don't forget to press CTRL-SHIFT-ENTER to save it after you are done editing the cell, otherwise it won't be saved as an array formula!)

How does it work?
  1. The array formula starts at cell A1 and works its way down to cell A65536.  Along the way it performs ISBLANK() on each cell it encounters.  ISBLANK() evaluates to true for each blank (empty) cell.
  2. Surrounding that, we have a NOT() function, which reverses the result from ISBLANK giving us false for blank cells and true for non-blank cells.  We now have a list of which cells are blank and which are non-blank.
  3. We then multiply that result by the ROW() number of each cell in the range, which is the same row number you see down the left side of the spreadsheet.  Since false is equivalent to zero and true is equivalent to one, this has the effect of multiplying the row number for each blank cell by zero and each non-blank cell by one.  We now have a value of zero for each blank cell, and the value of the row number for each non-blank cell.
  4. Finally, we take the MAX() of the list, which gives us the largest number in the list.  The largest number will be the last row used in the sheet.

    at