First and Last Row in a Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Simon,

The first row in the range is range1.row
The last row in the range is range1.row + range1.rows.count - 1
 
Simon said:
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
Click to expand...

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
 
Thanks Leo, worked perfectly.

Leo Heuser said:
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.
Click to expand...
 
Back
Top