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 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. This code works with the used range in the active sheet.

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

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