Start/End Cells in a Range

[Jay]

Hello all,

Having issues with a range and my searching has turned up nothing. I'm
probably missing something obvious, but I don't feel like beating my head
against the wall anymore.

I have a Range object. I'd like to get the row/column values for the start
and end cells of the range. The range is 'Raw Data'!$V$30:$V$51.

To retrieve $V$30, I can use Range.Row (=30) and Range.Column (=22).

How can I retrieve (or calculate) the row/column values for $V$51?

Any suggestions are appreciated.

Thanks,
Jay

 

[Chip Pearson]

Try something like

Dim TheRange As Range
Dim FirstCell As Range
Dim LastCell As Range

Set TheRange = Range("V30:V51")
Set FirstCell = TheRange(1, 1)
Set LastCell = TheRange(TheRange.Cells.Count)
Debug.Print "first: " & FirstCell.Address, _
"last: " & LastCell.Address


You can get the row/column from FirstCell/LastCell by using the Row
and Column properties.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Rick Rothstein]

Just noting that these slightly shorter Set commands will also work...

Set FirstCell = TheRange(1)
Set LastCell = TheRange(TheRange.Count)