From the Not Just Numbers blog:

Do you ever have a need to find the last row in a set of data – or the first gap?

I find that I use this tip quite regularly. It might be to help define a range to copy, or set a print area, or to identify where to enter the next row of data, or it might simply be that the last item in a list is highly relevant to the spreadsheet I’m developing.

Whatever the purpose, it is very handy to have a formula that will give me this information.

Say we want to know the first blank row in column A. We can use an array formula to return the row numbers of all the blank rows.

I will not go too much into array formulae here as that would be a significant blog post in itself, but there are three important things to know:

  1. An array is a set of arguments or results, arranged into rows and columns, operations can be applied to the whole array
  2. The result of an array formula will usually be an array itself and therefore will need another function to specify how it is to be shown as a single result (without this, the first result of the array will be displayed). Typical functions might be SUM or AVERAGE.
  3. To enter an array formula, you need to press Ctrl+Shift+Enter, rather than just Enter
Our array formula to return the numbers of all of the blank cells in column A would be:
=IF(A:A=””,ROW(A:A))
when we press Ctrl+Shift+Enter it will show as:
{=IF(A:A=””,ROW(A:A))}
Note that entering these curly brackets manually will not work, they must be generated by using Ctrl+Shift+Enter.
This formula will return the array containing all of the row numbers that are blank, separated by FALSE, where they are not. So, if the first 6 rows of A were:
A1 23
A2 65
A3
A4 47
A5 12
A6
Then the formula would return the array {FALSE,FALSE,3,FALSE,FALSE,,6}
However, all we will see in the cell is the first result, i.e. FALSE. What we need is a function to return the result we do want to see, which is the row number of the first blank row. This will also, of course, be the smallest (or minimum) number in the array. Excel provides the MIN function for just this purpose, so:
{=MIN(IF(A:A=””,ROW(A:A))}
will return 3, being the first blank row. Don’t forget to use Ctrl+Shift+Enter to generate the curly brackets.
We can use the same logic to find the last non-blank row, which is often even more useful.
This time we want the highest (maximum) row number, where the contents are not blank, so our formula would be:
{=MAX(IF(A:A<>””,ROW(A:A))}

 

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.