First and Last Cell Address in a Range

Following on from my last two VBA posts here is how you can identify the cell addresses of the first and last cells in a range.

In code example below we are using a message box to  display the first and last cell addresses in the used range for the active sheet.

Sub Test()
 
'display the first and last cell address in the used range
 
Dim rng As Range
 
Set rng = ActiveSheet.UsedRange
 
MsgBox rng(1).Address & " to " & _
    'code changed as per comment below - thanks to Rick Rothstein
    'rng.Cells(rng.Rows.Count, rng.Columns.Count).Address
     rng.Cells(rng.Count).Address
Set rng = Nothing
 
End Sub

These cells may be blank, it depends on the structure of the data in the sheet.

Used Range

The first cell in the used range is the intersection of the first used row and the first used column.

The last cell in the used range is the intersection of the last used row and the last used column.

Related Posts

Posted on
Categories: Excel Blog, Macros

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

5 thoughts on “First and Last Cell Address in a Range”

  1. Hi,

    I came upon this post and after playing around with the code you posted, I found out that Range property has a built-in property called Cells. This property gives the same data as the code above, so

    Sub Test()
    ‘display the first and last cell address in the used range
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    MsgBox rng.Cells
    Set rng = Nothing
    End Sub

    Reply

    • Hi Kris

      Sorry I couldn’t get that to work. You need .Address after Cells.

      Sub Test2()
      ‘display the first and last cell address in the used range
      Dim rng As Range
      Set rng = ActiveSheet.UsedRange
      MsgBox rng.Cells.Address
      Set rng = Nothing
      End Sub

      Its close, but slightly different. That code displays the range reference of the used range. A single line of code will also do that

      MsgBox ActiveSheet.UsedRange.Address

      In my original code I have separately identified the first and last cell references of the used range and then joined them together with the word “to”.

      Hope that helps.
      Neale

      Reply

      • Hi Neale,

        Good job on catching the mistake on my code. I actually used a part of your code to select the last cell of a range.

        Have a great day,
        Kris

        Reply

  2. You do not need to use this to calculate the last cell in the range’s address…

    rng.Cells(rng.Rows.Count, rng.Columns.Count).Address

    you can use this shorter method instead…

    rng.Cells(rng.Count).Address

    Reply