Hi All,

I have a one column wide non-contiguous selection. I want to determine the
row No of the last cell in the selection (the highest row No in the
selection).
How can I do that?

Thanks,
Stefi

MsgBox Selection(Selection.Count).Row

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Stefi" wrote in message
...
Hi All,

I have a one column wide non-contiguous selection. I want to determine the
row No of the last cell in the selection (the highest row No in the
selection).
How can I do that?

Thanks,
Stefi

Since you could have more than one area selected,
and the last area selected may be above one of
the other areas...

Try something like this:

Sub LastSelRow()
Dim rArea As Range
Dim iBullpen
Dim iLastSelRow As Single

For Each rArea In Selection.Areas
iBullpen = rArea.Row + rArea.Rows.Count - 1
If iBullpen iLastSelRow Then
iLastSelRow = iBullpen
End If
Next rArea
MsgBox iLastSelRow
End Sub

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Stefi" wrote in message
...
Hi All,

I have a one column wide non-contiguous selection. I want to determine the
row No of the last cell in the selection (the highest row No in the
selection).
How can I do that?

Thanks,
Stefi

If your selection includes the last entry in the column, you can use:

Range("A" & Rows.Count).End(xlUp).Row

(change "A" to the correct column).
Here is another approach, which will work whether your selection includes
the last entry in the column or not:

Selection.Cells(1, 1).Row + Selection.Rows.Count - 1

Hope this helps,

Hutch

"Stefi" wrote:

Hi All,

I have a one column wide non-contiguous selection. I want to determine the
row No of the last cell in the selection (the highest row No in the
selection).
How can I do that?

Thanks,
Stefi

I wanted to use:

Dim myRng As Range
Dim MaxRow As Long

With ActiveSheet
Set myRng = Intersect(.Columns(1), Selection.EntireRow)
End With

With myRng
With .Areas(.Areas.Count)
MaxRow = .Cells(.Cells.Count).Row
End With
End With

MsgBox MaxRow

But depending on how the range is selected, that last area in the range wasn't
always the bottommost area. (and so the last cell in the last area wasn't
always the bottommost row).

But this worked. It just loops through all the areas and looks at the last row
of each area.

Dim myRng As Range
Dim MaxRow As Long
Dim myArea As Range

With ActiveSheet
Set myRng = Intersect(.Columns(1), Selection.EntireRow)
End With

MaxRow = 0
For Each myArea In myRng.Areas
With myArea
If .Cells(.Cells.Count).Row MaxRow Then
MaxRow = .Cells(.Cells.Count).Row
End If
End With
Next myArea

MsgBox MaxRow

=====
I didn't need to do the intersect() stuff, but it's nice if the selection spans
multiple columns.



Stefi wrote:

Hi All,

I have a one column wide non-contiguous selection. I want to determine the
row No of the last cell in the selection (the highest row No in the
selection).
How can I do that?

Thanks,
Stefi

--

Dave Peterson

Sorry Bob, it doesn't do what I mean!

Eg. A2,A5,A7,A9 are selected. I need 9 as row No of A9 (the highest row No
in selection).
Selection(Selection.Count).Row returns 5, I don't know why.

Regards,
Stefi


€žBob Phillips€� ezt Ã*rta:

MsgBox Selection(Selection.Count).Row

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Stefi" wrote in message
...
Hi All,

I have a one column wide non-contiguous selection. I want to determine the
row No of the last cell in the selection (the highest row No in the
selection).
How can I do that?

Thanks,
Stefi

Thanks, Ron and Tom, Ron's approach worked, but Tom's formulae work only with
contiguous ranges (Selection.Rows.Count doesn't return the correct No of
cells in a non-contiguous range).
I thought there is a simpler way than looping through the range one by one.

Regards,
Stefi


€žRon Coderre€� ezt Ã*rta:

Since you could have more than one area selected,
and the last area selected may be above one of
the other areas...

Try something like this:

Sub LastSelRow()
Dim rArea As Range
Dim iBullpen
Dim iLastSelRow As Single

For Each rArea In Selection.Areas
iBullpen = rArea.Row + rArea.Rows.Count - 1
If iBullpen iLastSelRow Then
iLastSelRow = iBullpen
End If
Next rArea
MsgBox iLastSelRow
End Sub

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Stefi" wrote in message
...
Hi All,

I have a one column wide non-contiguous selection. I want to determine the
row No of the last cell in the selection (the highest row No in the
selection).
How can I do that?

Thanks,
Stefi

Like you, I wish the solution was simpler, but ....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Stefi" wrote in message
...
Thanks, Ron and Tom, Ron's approach worked, but Tom's formulae work only
with
contiguous ranges (Selection.Rows.Count doesn't return the correct No of
cells in a non-contiguous range).
I thought there is a simpler way than looping through the range one by
one.

Regards,
Stefi


"Ron Coderre" ezt írta:

Since you could have more than one area selected,
and the last area selected may be above one of
the other areas...

Try something like this:

Sub LastSelRow()
Dim rArea As Range
Dim iBullpen
Dim iLastSelRow As Single

For Each rArea In Selection.Areas
iBullpen = rArea.Row + rArea.Rows.Count - 1
If iBullpen iLastSelRow Then
iLastSelRow = iBullpen
End If
Next rArea
MsgBox iLastSelRow
End Sub

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Stefi" wrote in message
...
Hi All,

I have a one column wide non-contiguous selection. I want to determine
the
row No of the last cell in the selection (the highest row No in the
selection).
How can I do that?

Thanks,
Stefi

On Fri, 28 Mar 2008 06:38:00 -0700, Stefi
wrote:

Hi All,

I have a one column wide non-contiguous selection. I want to determine the
row No of the last cell in the selection (the highest row No in the
selection).
How can I do that?

Thanks,
Stefi

You do write that you have the entire column selected.

In that case:

=============
Option Explicit
Sub lastrow()
Dim c As Range
Dim lLastRow As Long
Set c = Cells(Selection.Rows.Count, Selection.Column)
Set c = c.End(xlUp)
lLastRow = c.Row

Debug.Print lLastRow

End Sub
======================

or

=====================
Sub lastrow()
Dim lLastRow As Long

lLastRow = Cells(Selection.Rows.Count, Selection.Column).End(xlUp).Row

Debug.Print lLastRow
End Sub
===============
--ron

Hi Ron,

Thank you for your reply! The problem is that my request was
misunderstandable. I didn't mean that the entire column is selected, but all
selected cells are in the same column, e.g. A3,A5,A8, etc. The only solution
up to now is Ron Coderre's approach.

Regards,
Stefi


€žRon Rosenfeld€� ezt Ã*rta:

On Fri, 28 Mar 2008 06:38:00 -0700, Stefi
wrote:

Hi All,

I have a one column wide non-contiguous selection. I want to determine the
row No of the last cell in the selection (the highest row No in the
selection).
How can I do that?

Thanks,
Stefi

You do write that you have the entire column selected.

In that case:

=============
Option Explicit
Sub lastrow()
Dim c As Range
Dim lLastRow As Long
Set c = Cells(Selection.Rows.Count, Selection.Column)
Set c = c.End(xlUp)
lLastRow = c.Row

Debug.Print lLastRow

End Sub
======================

or

=====================
Sub lastrow()
Dim lLastRow As Long

lLastRow = Cells(Selection.Rows.Count, Selection.Column).End(xlUp).Row

Debug.Print lLastRow
End Sub
===============
--ron

On Mon, 31 Mar 2008 06:09:00 -0700, Stefi
wrote:

Hi Ron,

Thank you for your reply! The problem is that my request was
misunderstandable. I didn't mean that the entire column is selected, but all
selected cells are in the same column, e.g. A3,A5,A8, etc. The only solution
up to now is Ron Coderre's approach.

Regards,
Stefi

Well, here's another approach:

==============================
Option Explicit
Sub lLastrow()
Dim Addr, Temp
Temp = Replace(Selection.Address, ":", ",")
Temp = Replace(Temp, "$", ",")
Addr = Split(Temp, ",")
Debug.Print "Last Row:", Addr(UBound(Addr))
End Sub
=================================

And it could be done as a "one-liner" but might not be as understandable.
--ron

Ron

If the cell areas are not selected in descending order,
the code returns the last row of the last area that was selected,
instead of the max last row of all selected areas.

Example:
Select A1:A5
then A15:A20
then A10:A12

When the code runs, it returns:
"Last Row: 12"

instead of
"Last Row: 20"
(The last row of the 2nd area selected)

Is that what the Op is looking for?

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)





"Ron Rosenfeld" wrote in message
...
On Mon, 31 Mar 2008 06:09:00 -0700, Stefi

wrote:

Hi Ron,

Thank you for your reply! The problem is that my request was
misunderstandable. I didn't mean that the entire column is selected, but
all
selected cells are in the same column, e.g. A3,A5,A8, etc. The only
solution
up to now is Ron Coderre's approach.

Regards,
Stefi

Well, here's another approach:

==============================
Option Explicit
Sub lLastrow()
Dim Addr, Temp
Temp = Replace(Selection.Address, ":", ",")
Temp = Replace(Temp, "$", ",")
Addr = Split(Temp, ",")
Debug.Print "Last Row:", Addr(UBound(Addr))
End Sub
=================================

And it could be done as a "one-liner" but might not be as understandable.
--ron

On Mon, 31 Mar 2008 12:13:01 -0400, "Ron Coderre"
wrote:

Ron

If the cell areas are not selected in descending order,
the code returns the last row of the last area that was selected,
instead of the max last row of all selected areas.

Example:
Select A1:A5
then A15:A20
then A10:A12

When the code runs, it returns:
"Last Row: 12"

instead of
"Last Row: 20"
(The last row of the 2nd area selected)

Is that what the Op is looking for?

I think you are correct. Mine approach could be modified to handle that:

=====================
Option Explicit
Sub lLastrow()
Dim Addr, Temp
Dim i As Long
Temp = Replace(Selection.Address, ":", ",")
Temp = Replace(Temp, "$", ",")
Addr = Split(Temp, ",")
Temp = 0
For i = 0 To UBound(Addr)
If Val(Addr(i)) Temp Then Temp = Val(Addr(i))
Next i
Debug.Print "Last Row:", Temp
End Sub
====================
--ron

Thanks, guys for analysing my problem so deep! I really didn't think it's so
complicated job!

Regards,
Stefi


€žRon Rosenfeld€� ezt Ã*rta:

On Mon, 31 Mar 2008 12:13:01 -0400, "Ron Coderre"
wrote:

Ron

If the cell areas are not selected in descending order,
the code returns the last row of the last area that was selected,
instead of the max last row of all selected areas.

Example:
Select A1:A5
then A15:A20
then A10:A12

When the code runs, it returns:
"Last Row: 12"

instead of
"Last Row: 20"
(The last row of the 2nd area selected)

Is that what the Op is looking for?

I think you are correct. Mine approach could be modified to handle that:

=====================
Option Explicit
Sub lLastrow()
Dim Addr, Temp
Dim i As Long
Temp = Replace(Selection.Address, ":", ",")
Temp = Replace(Temp, "$", ",")
Addr = Split(Temp, ",")
Temp = 0
For i = 0 To UBound(Addr)
If Val(Addr(i)) Temp Then Temp = Val(Addr(i))
Next i
Debug.Print "Last Row:", Temp
End Sub
====================
--ron