Find the first and last row in a range 2

[daveonion]

Hi i have a range and i want to use the first and last row in that range, i know theres the range.end(xlup).row command but that appears to return it for the whole sheet and not just a declared range, any ideas thanks

 

[PHV]

You may play with the Range.Address property and the Split function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[daveonion]

Hi PH, im still unsure how to do it, could you expand the explanation a bit more please
thanks

 

[TonyJollans]

I'd use:

[blue]rng.Rows(rng.Rows.Count)[/blue]

or, if you want the whole row:

[blue]rng.Rows(rng.Rows.Count).EntireRow[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[daveonion]

Hi Tony, sorry what i mean is the following
A user will select a range for an inputbox, i then need to do a calculation based on what was selected in the row, to do the calculation i need to know what the first row in the range is and the last row i.e. range("B5:B10") So the first row is 5 and the last 10,
thanks in advance

 

[daveonion]

thanks tony finally realised what u meant

 

[electricpete]

Let's say the user selected B5:H10

The first row in myrange can be referenced as:
myrange.rows(1)

(would return B5:H5)

The last row in the range can be referenced as:
myrange.rows(myrange.rows.count)
(would return B10:H10)


Note your discussion "B5:B10" is a column.... I assume that was a typo?

 

[SkipVought]

"A user will select a range for an inputbox..."

with selection
  lFirstRow = .row
  lLastRow = .rows.count - .row + 1
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[electricpete]

I think Skip read the question better than I did. My answer was giving you reference to ranges of the first row of selection and last row of selection. He is giving you the row number which sounds like more what you wanted.
Sorry for not reading the question.

 

[electricpete]

Actually, let me try once more to answer what should have been a very simple question (sorry if I'm making it more complicated.)

range("B5:B10") So the first row is 5 and the last 10,

The first row is selection.row
(5)
The last row is selection(selection.rows.count).row
(10)

 

[electricpete]

naturally, if the range you are interested in is passed by messagebox instead of the current selection, you need to substittue "myrange" or some similar variable instead of selection.

 

[electricpete]

Wow, did I screw up again. I am truly embarassed. Above works for single column range but not multiple. Of course it should go like this:
The first row is selection.row
(5)
The last row is selection.rows(selection.rows.count).row
(10)

 

[electricpete]

Skip was also working along the same lines, but
lLastRow = .rows.count - .row + 1
should have been
lLastRow = .rows.count + .row - 1

 

[SkipVought]

By Bad, you're correct.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[daveonion]

Hi everyone thanks for your replies, I had sussed it from Tony's original comments I used
range.row and range.rows.count + 1
thanks again