First and Last Row in a Range
 

Hi There,

I have two ranges, range1 is cells C5:C10, I then get the user to enter a
column letter, and what I need is for some code to select range based on the
column letter and the first and last rows selected in range1.

For example, if the users enters 'A' then range2 would be A5:A10, I don't
know how to extract the first and last row numbers out of range1 (i.e the
value 5 and the value 10).

If someone can help me out or has a better way of doing it, then that would
be great.

Thanks.
Simon

First and Last Row in a Range
 

Simon,

The first row in the range is range1.row
The last row in the range is range1.row + range1.rows.count - 1

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Simon" wrote:

Hi There,

I have two ranges, range1 is cells C5:C10, I then get the user to enter a
column letter, and what I need is for some code to select range based on the
column letter and the first and last rows selected in range1.

For example, if the users enters 'A' then range2 would be A5:A10, I don't
know how to extract the first and last row numbers out of range1 (i.e the
value 5 and the value 10).

If someone can help me out or has a better way of doing it, then that would
be great.

Thanks.
Simon

First and Last Row in a Range
 

"Simon" skrev i en meddelelse
...
Hi There,

I have two ranges, range1 is cells C5:C10, I then get the user to enter a
column letter, and what I need is for some code to select range based on
the
column letter and the first and last rows selected in range1.

For example, if the users enters 'A' then range2 would be A5:A10, I don't
know how to extract the first and last row numbers out of range1 (i.e the
value 5 and the value 10).

If someone can help me out or has a better way of doing it, then that
would
be great.

Thanks.
Simon

Hi Simon

One way to do it:

Set Rng1 = Range("C5:C10")
Col = Application.InputBox("Enter column-letter")
Set Rng2 = Rng1.Offset(0, Columns(Col).Column - Rng1.Column)
Rng2.Select


--
Best regards
Leo Heuser

Followup to newsgroup only please.

First and Last Row in a Range
 

Thanks Leo, worked perfectly.

"Leo Heuser" wrote:

"Simon" skrev i en meddelelse
...
Hi There,

I have two ranges, range1 is cells C5:C10, I then get the user to enter a
column letter, and what I need is for some code to select range based on
the
column letter and the first and last rows selected in range1.

For example, if the users enters 'A' then range2 would be A5:A10, I don't
know how to extract the first and last row numbers out of range1 (i.e the
value 5 and the value 10).

If someone can help me out or has a better way of doing it, then that
would
be great.

Thanks.
Simon

Hi Simon

One way to do it:

Set Rng1 = Range("C5:C10")
Col = Application.InputBox("Enter column-letter")
Set Rng2 = Rng1.Offset(0, Columns(Col).Column - Rng1.Column)
Rng2.Select


--
Best regards
Leo Heuser

Followup to newsgroup only please.

First and Last Row in a Range
 

"Simon" skrev i en meddelelse
...
Thanks Leo, worked perfectly.

You're welcome, Simon. Thanks for the feedback.

Leo Heuser