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 " & _
    rng.Cells(rng.Rows.Count, rng.Columns.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.

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. Required fields are marked *

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