First and Last Row in a Range

[Guest]

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

 

[Guest]

Simon,

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

 

[Leo Heuser]

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

 

[Guest]

Thanks Leo, worked perfectly.

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.

 

[Leo Heuser]

Thanks Leo, worked perfectly.

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

Leo Heuser