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:
- An array is a set of arguments or results, arranged into rows and columns, operations can be applied to the whole array
- 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.
- 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".
On this same topic, I often use END plus the down arrow key. that will put the cursor at the bottom of a column of data. You can also use the right (or left) arrow to get to the last data entry in a row. If highlighting, then use Shift + END + the desired arrow key.
ReplyDeleteThanks for that John. That's very useful to move to the bottom of a column while editing the spreadsheet, whereas my formula above is to return the position as a variable that can be used by the spreadsheet.
DeleteThere are several methods to accomplish this, some more exact than others, but they can all serve your needs.
ReplyDeleteOne common method
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
which is not very exact, because Excel doesn't keep track of the last cell in a very adequate form.
Another method to find the last used row in a particular column is:
LastRowColA = Range("A65536").End(xlUp).Row
but this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data.
A couple extra methods are more reliable.
LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
or
LastRow = ActiveSheet.UsedRange.Rows.Count
This methods can be used on any sheet, not just the active sheet.
Thanks BeenLetDown, they're all good tips if you want to find the last row when using VBA. My post explains how to do it in a formula.
Delete