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.
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
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
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
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
Hi Rick
Thanks for that I will update the post. Love your work.
Regards
Neale