VBA to Find the Row and Column Number Limits of a Range

First and last row and column numbers

In my last blog post I found the last used row and column numbers on the active sheet. This post lets us find the row and column extremities of a specific range.

If you are using a variable to handle a range then you may need to identify what the first and last row and column numbers are that make up that range.

The code below demonstrates how to use capture the row and column numbers. A message box displays the results. You can download a file with the code at the button at the bottom of the post.

Sub test()
Dim rng As Range
Dim lFirstRow, lFirstCol, lLastRow, lLastCol As Long
Set rng = ActiveSheet.UsedRange
'First row number
lFirstRow = rng(1).Row
'First column number
lFirstCol = rng(1).Column
'Last row number
lLastRow = rng(rng.Rows.Count, 1).Row
'Last column number
lLastCol = rng(1, rng.Columns.Count).Column
MsgBox rng.Address & " = " & lFirstCol & _
    " , " & lFirstRow & " , " & lLastCol & " , " & lLastRow
Set rng = Nothing
End Sub

Download Example File

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.

4 thoughts on “VBA to Find the Row and Column Number Limits of a Range