Get The Last Used Row In VBA Using Range.End

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 Range.End:

  • Range.End(xlUp)
  • Range.End(xlDown)

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.

About these ads

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2013
This entry was posted in Microsoft Excel, Microsoft Office and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s