Prev: Textbox object event - Help please
Next: RowSource for my ListBox
From: Jay on 12 Apr 2010 13:03
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
--
Disregard, this is so I can find my post later.
***postedbyJay***
From: Chip Pearson on 12 Apr 2010 13:10

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




On Mon, 12 Apr 2010 10:03:01 -0700, Jay
<Jay(a)discussions.microsoft.com> wrote:

>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
From: Rick Rothstein on 12 Apr 2010 13:39
Just noting that these slightly shorter Set commands will also work...

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

--
Rick (MVP - Excel)



"Chip Pearson" <chip(a)cpearson.com> wrote in message
news:huk6s5p1sqlmttpdffnmoq0bnd6gq688jj(a)4ax.com...
>
> 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
>
>
>
>
> On Mon, 12 Apr 2010 10:03:01 -0700, Jay
> <Jay(a)discussions.microsoft.com> wrote:
>
>>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

 | 
Pages: 1
Prev: Textbox object event - Help please
Next: RowSource for my ListBox