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