last row of a non-contiguous selection

  • Thread starter Thread starter Stefi
  • Start date Start date
S

Stefi

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)
 
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)
 
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
 
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.
 
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:
 
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:
 
Like you, I wish the solution was simpler, but ....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
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
Click to expand...

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:
 
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
Click to expand...

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

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?
Click to expand...

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:
 
Back
Top