In the last post we saw a really flexible function which can pull out the last used row from a range of any dimensions. Sometimes I use a different approach though: if I know I want to look at only a single column then I might well use the
Range.End property instead. Why? Well, quite simply, it’s faster.
In terms of looking for the last row of data there are two possible ways we can use
These two mimic your manual actions on a worksheet when you press CTRL+[UP ARROW] and CTRL+[DOWN ARROW] respectively.
So, should we start at the top of the column and look downwards, or start at the bottom of the column and look upwards? Well, the problem with starting at the top is that if there are any gaps in the data then
Range.End(xlDown) will find the gap and not the last used row. For that reason it’s best to start at the bottom and work up. To get the bottom of the column we can use
Worksheet.Rows.Count, which will return 65,536 if we have an Excel 97-03 worksheet or 1,048,576 if we have an Excel 2007+ worksheet. If the entire column is empty then you’ll get 1 in the Immediate window (in the VBA IDE press CTRL+G to show the Immediate window) when you run this code:
Sub Example1() Dim rngLastCell As Range 'what is the last cell (with or without data) in Sheet1!A:A ? Set rngLastCell = Sheet1.Cells(Sheet1.Rows.Count, "A") 'what is the last used row in Sheet1!A:A ? Debug.Print rngLastCell.End(xlUp).Row End Sub
One small detail is that if the very last cell in the column contains data then this piece of code will miss it. This is a very unusual situation so normally VBA’ers ignore it but, if it is a concern to you, then you’ll need to add an extra check:
Sub Example2() Dim rngLastCell As Range 'what is the last cell (with or without data) in Sheet1!A:A ? Set rngLastCell = Sheet1.Cells(Sheet1.Rows.Count, "A") 'what is the last used row in Sheet1!A:A ? If IsEmpty(rngLastCell.Value2) Then 'the last cell is empty so we look upwards Debug.Print rngLastCell.End(xlUp).Row Else 'the last cell has data so it is the one we want Debug.Print rngLastCell.Row End If End Sub
Finally, be aware that
Range.End will not correctly find the last used row in a column if that row has been hidden – for example, by an autofilter.