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
Worked for me with named ranges!! thank you!
Glad it worked
Thank you. Thank you very much ! It’s exactly what I was looking for . . .
Cool Grigore – glad it helped.