No question here, just a procedure for the archive.

Search critera: activate the last cell in a selection
select last cell in range
select last cell in selection
activate last cell in range


Sub SelectActivateLastCellInSelectedRange()
'Activates the last cell in the selected range but keeps the
'same range selected


Dim LastRow As Variant
Dim LastCol As Variant
Dim TempRow As Variant
Dim TempCol As Variant
Dim LastCell As Range
Dim A As Range


LastRow = 1
LastCol = 1
ActiveWorkbook.Activate
For Each A In Selection.Areas
TempRow = A.Range("A1").Offset(A.Rows.Count - 1, 0).Row
If TempRow LastRow Then LastRow = TempRow
TempCol = A.Range("A1").Offset(0, A.Columns.Count - 1).Column
If TempCol LastCol Then LastCol = TempCol
Next A
Set LastCell = Cells(LastRow, LastCol)
LastCell.Activate


End Sub

Or, you can use:
Sub SelectActivateLastCellInSelectedRange()
Selection.Cells(Selection.Cells.Count).Select
End Sub

Bob Umlas
Excel MVP

"DataFreakFromUtah" wrote in message
om...
No question here, just a procedure for the archive.

Search critera: activate the last cell in a selection
select last cell in range
select last cell in selection
activate last cell in range


Sub SelectActivateLastCellInSelectedRange()
'Activates the last cell in the selected range but keeps the
'same range selected


Dim LastRow As Variant
Dim LastCol As Variant
Dim TempRow As Variant
Dim TempCol As Variant
Dim LastCell As Range
Dim A As Range


LastRow = 1
LastCol = 1
ActiveWorkbook.Activate
For Each A In Selection.Areas
TempRow = A.Range("A1").Offset(A.Rows.Count - 1, 0).Row
If TempRow LastRow Then LastRow = TempRow
TempCol = A.Range("A1").Offset(0, A.Columns.Count - 1).Column
If TempCol LastCol Then LastCol = TempCol
Next A
Set LastCell = Cells(LastRow, LastCol)
LastCell.Activate


End Sub

Hi DataFreakFromUtah,

In addition to Bob's response, for a multiple area selection, try:

Sub Tester()
Dim i As Long
i = Selection.Areas.Count

With Selection
.Areas(i).Cells(.Areas(i).Cells.Count).Activate
End With
End Sub


---
Regards,
Norman



"DataFreakFromUtah" wrote in message
om...
No question here, just a procedure for the archive.

Search critera: activate the last cell in a selection
select last cell in range
select last cell in selection
activate last cell in range


Sub SelectActivateLastCellInSelectedRange()
'Activates the last cell in the selected range but keeps the
'same range selected


Dim LastRow As Variant
Dim LastCol As Variant
Dim TempRow As Variant
Dim TempCol As Variant
Dim LastCell As Range
Dim A As Range


LastRow = 1
LastCol = 1
ActiveWorkbook.Activate
For Each A In Selection.Areas
TempRow = A.Range("A1").Offset(A.Rows.Count - 1, 0).Row
If TempRow LastRow Then LastRow = TempRow
TempCol = A.Range("A1").Offset(0, A.Columns.Count - 1).Column
If TempCol LastCol Then LastCol = TempCol
Next A
Set LastCell = Cells(LastRow, LastCol)
LastCell.Activate


End Sub

Norman,

I came up with something like yours, but it always selects the bottom right
corner of the last area selected, i.e., if I last area I selected ("last"
temporally speaking) is above and to the left of other selections, it still
activates it's last cell.

The only issue I have with DFFU's is that it sometimes chooses a cell
outside of any of the area, e.g., if you select A1:B3 and D1:D2 it "squares
the corner." However, I can't come up with anything better.

Regards,

Doug Glancy

"Norman Jones" wrote in message
...
Hi DataFreakFromUtah,

In addition to Bob's response, for a multiple area selection, try:

Sub Tester()
Dim i As Long
i = Selection.Areas.Count

With Selection
.Areas(i).Cells(.Areas(i).Cells.Count).Activate
End With
End Sub


---
Regards,
Norman



"DataFreakFromUtah" wrote in message
om...
No question here, just a procedure for the archive.

Search critera: activate the last cell in a selection
select last cell in range
select last cell in selection
activate last cell in range


Sub SelectActivateLastCellInSelectedRange()
'Activates the last cell in the selected range but keeps the
'same range selected


Dim LastRow As Variant
Dim LastCol As Variant
Dim TempRow As Variant
Dim TempCol As Variant
Dim LastCell As Range
Dim A As Range


LastRow = 1
LastCol = 1
ActiveWorkbook.Activate
For Each A In Selection.Areas
TempRow = A.Range("A1").Offset(A.Rows.Count - 1, 0).Row
If TempRow LastRow Then LastRow = TempRow
TempCol = A.Range("A1").Offset(0, A.Columns.Count - 1).Column
If TempCol LastCol Then LastCol = TempCol
Next A
Set LastCell = Cells(LastRow, LastCol)
LastCell.Activate


End Sub

Hi Doug,

I came up with something like yours, but it always selects the bottom
right
corner of the last area selected

Ageed

The only issue I have with DFFU's is that it sometimes chooses a cell
outside of any of the area

Also agreed.

The following seemed to resolve the above and work consistently with
single/multiple area ranges, independently of selection sequence or area
intersection/overlap:

Sub Test1()
Dim Rng1 As Range, Rng2 As Range
Dim i As Long

With Selection
Set Rng1 = .Areas(1)(.Areas(1).Cells.Count)
For i = 2 To .Areas.Count
Set Rng2 = .Areas(i)(.Areas(i).Cells.Count)
If Rng2.Address Rng1.Address _
Then Set Rng1 = Rng2
Next
End With
Rng1.Activate
End Sub

---
Regards,
Norman



"Doug Glancy" wrote in message
...
Norman,

I came up with something like yours, but it always selects the bottom
right
corner of the last area selected, i.e., if I last area I selected ("last"
temporally speaking) is above and to the left of other selections, it
still
activates it's last cell.

The only issue I have with DFFU's is that it sometimes chooses a cell
outside of any of the area, e.g., if you select A1:B3 and D1:D2 it
"squares
the corner." However, I can't come up with anything better.

Regards,

Doug Glancy

"Norman Jones" wrote in message
...
Hi DataFreakFromUtah,

In addition to Bob's response, for a multiple area selection, try:

Sub Tester()
Dim i As Long
i = Selection.Areas.Count

With Selection
.Areas(i).Cells(.Areas(i).Cells.Count).Activate
End With
End Sub


---
Regards,
Norman



"DataFreakFromUtah" wrote in message
om...
No question here, just a procedure for the archive.

Search critera: activate the last cell in a selection
select last cell in range
select last cell in selection
activate last cell in range


Sub SelectActivateLastCellInSelectedRange()
'Activates the last cell in the selected range but keeps the
'same range selected


Dim LastRow As Variant
Dim LastCol As Variant
Dim TempRow As Variant
Dim TempCol As Variant
Dim LastCell As Range
Dim A As Range


LastRow = 1
LastCol = 1
ActiveWorkbook.Activate
For Each A In Selection.Areas
TempRow = A.Range("A1").Offset(A.Rows.Count - 1, 0).Row
If TempRow LastRow Then LastRow = TempRow
TempCol = A.Range("A1").Offset(0, A.Columns.Count - 1).Column
If TempCol LastCol Then LastCol = TempCol
Next A
Set LastCell = Cells(LastRow, LastCol)
LastCell.Activate


End Sub

Doug certainly points out two of the major weaknesses in trying to solve
this problem. The OP definitely misses the boat by selecting a cell outside
the original range (depending on the selection) and using areas.count
doesn't work because a multi-area selection is organized in the order the
selection was made. That can certainly be attacked with something like

Sub AAA()
Dim lowerRight As Range
Dim ar As Range
Dim rng as Range

Set lowerRight = Range("A1")
For Each ar In Selection.Areas
Set rng = ar(ar.Count)
If rng.Row = lowerRight.Row And _
rng.Column = lowerRight.Column Then
Set lowerRight = rng
End If
Next
lowerRight.Activate
End Sub



but this highlights a more basic weakness in that there is no clear
definition of what constitutes the last cell. Is it the cell farthest to the
right or the cell in the highest numbered row. If I have B1 and A2
selected, which is the last cell. The above will end up on one extreme, but
which would depend a lot on order of selection.


At least the OP has archived another less than perfect routine for all to
enjoy.

--
Regards,
Tom Ogilvy



"Doug Glancy" wrote in message
...
Norman,

I came up with something like yours, but it always selects the bottom
right
corner of the last area selected, i.e., if I last area I selected ("last"
temporally speaking) is above and to the left of other selections, it
still
activates it's last cell.

The only issue I have with DFFU's is that it sometimes chooses a cell
outside of any of the area, e.g., if you select A1:B3 and D1:D2 it
"squares
the corner." However, I can't come up with anything better.

Regards,

Doug Glancy

"Norman Jones" wrote in message
...
Hi DataFreakFromUtah,

In addition to Bob's response, for a multiple area selection, try:

Sub Tester()
Dim i As Long
i = Selection.Areas.Count

With Selection
.Areas(i).Cells(.Areas(i).Cells.Count).Activate
End With
End Sub


---
Regards,
Norman



"DataFreakFromUtah" wrote in message
om...
No question here, just a procedure for the archive.

Search critera: activate the last cell in a selection
select last cell in range
select last cell in selection
activate last cell in range


Sub SelectActivateLastCellInSelectedRange()
'Activates the last cell in the selected range but keeps the
'same range selected


Dim LastRow As Variant
Dim LastCol As Variant
Dim TempRow As Variant
Dim TempCol As Variant
Dim LastCell As Range
Dim A As Range


LastRow = 1
LastCol = 1
ActiveWorkbook.Activate
For Each A In Selection.Areas
TempRow = A.Range("A1").Offset(A.Rows.Count - 1, 0).Row
If TempRow LastRow Then LastRow = TempRow
TempCol = A.Range("A1").Offset(0, A.Columns.Count - 1).Column
If TempCol LastCol Then LastCol = TempCol
Next A
Set LastCell = Cells(LastRow, LastCol)
LastCell.Activate


End Sub

If B1 and AA1 are selected, this chooses B1

if B10, D1, AA1 are selected this chooses D1

But besides the weakness in an alphabetic comparison, the defintion of what
is the last cell remains a problem.

Maybe one needs to use polar coordinates. (but which is dominant, angle or
distance).

--
Regards,
Tom Ogilvy

"Norman Jones" wrote in message
...
Hi Doug,

I came up with something like yours, but it always selects the bottom
right
corner of the last area selected

Ageed

The only issue I have with DFFU's is that it sometimes chooses a cell
outside of any of the area

Also agreed.

The following seemed to resolve the above and work consistently with
single/multiple area ranges, independently of selection sequence or area
intersection/overlap:

Sub Test1()
Dim Rng1 As Range, Rng2 As Range
Dim i As Long

With Selection
Set Rng1 = .Areas(1)(.Areas(1).Cells.Count)
For i = 2 To .Areas.Count
Set Rng2 = .Areas(i)(.Areas(i).Cells.Count)
If Rng2.Address Rng1.Address _
Then Set Rng1 = Rng2
Next
End With
Rng1.Activate
End Sub

---
Regards,
Norman



"Doug Glancy" wrote in message
...
Norman,

I came up with something like yours, but it always selects the bottom
right
corner of the last area selected, i.e., if I last area I selected
("last"
temporally speaking) is above and to the left of other selections, it
still
activates it's last cell.

The only issue I have with DFFU's is that it sometimes chooses a cell
outside of any of the area, e.g., if you select A1:B3 and D1:D2 it
"squares
the corner." However, I can't come up with anything better.

Regards,

Doug Glancy

"Norman Jones" wrote in message
...
Hi DataFreakFromUtah,

In addition to Bob's response, for a multiple area selection, try:

Sub Tester()
Dim i As Long
i = Selection.Areas.Count

With Selection
.Areas(i).Cells(.Areas(i).Cells.Count).Activate
End With
End Sub


---
Regards,
Norman



"DataFreakFromUtah" wrote in message
om...
No question here, just a procedure for the archive.

Search critera: activate the last cell in a selection
select last cell in range
select last cell in selection
activate last cell in range


Sub SelectActivateLastCellInSelectedRange()
'Activates the last cell in the selected range but keeps the
'same range selected


Dim LastRow As Variant
Dim LastCol As Variant
Dim TempRow As Variant
Dim TempCol As Variant
Dim LastCell As Range
Dim A As Range


LastRow = 1
LastCol = 1
ActiveWorkbook.Activate
For Each A In Selection.Areas
TempRow = A.Range("A1").Offset(A.Rows.Count - 1, 0).Row
If TempRow LastRow Then LastRow = TempRow
TempCol = A.Range("A1").Offset(0, A.Columns.Count - 1).Column
If TempCol LastCol Then LastCol = TempCol
Next A
Set LastCell = Cells(LastRow, LastCol)
LastCell.Activate


End Sub

So here's my stab at it. I've assumed that the definition of the last cell
is whichever is farthest from A1, i.e., total rows + columns. In some cases
it matches Tom's, in others not (Tom's seems to favor columns). If it comes
up a tie it goes with the earlier solution:

Sub test()

Dim i As Long, area_distance_from_A1 As Long
Dim longest_distance As Long, last_area As Long

With Selection
For i = 1 To .Areas.Count
With .Areas(i)
Set area_last_cell = Range("A1:" & .Cells(.Cells.Count).Address)
area_distance_from_A1 = area_last_cell.Rows.Count +
area_last_cell.Columns.Count
If area_distance_from_A1 longest_distance Then
longest_distance = area_distance_from_A1
last_area = i
End If
End With
Next i
End With

With Selection.Areas(last_area)
.Cells(.Cells.Count).Activate
End With

End Sub

Thanks to DataFreak for an interesting problem,

Doug

"DataFreakFromUtah" wrote in message
om...
No question here, just a procedure for the archive.

Search critera: activate the last cell in a selection
select last cell in range
select last cell in selection
activate last cell in range


Sub SelectActivateLastCellInSelectedRange()
'Activates the last cell in the selected range but keeps the
'same range selected


Dim LastRow As Variant
Dim LastCol As Variant
Dim TempRow As Variant
Dim TempCol As Variant
Dim LastCell As Range
Dim A As Range


LastRow = 1
LastCol = 1
ActiveWorkbook.Activate
For Each A In Selection.Areas
TempRow = A.Range("A1").Offset(A.Rows.Count - 1, 0).Row
If TempRow LastRow Then LastRow = TempRow
TempCol = A.Range("A1").Offset(0, A.Columns.Count - 1).Column
If TempCol LastCol Then LastCol = TempCol
Next A
Set LastCell = Cells(LastRow, LastCol)
LastCell.Activate


End Sub

Hi Tom,

If B1 and AA1 are selected, this chooses B1

if B10, D1, AA1 are selected this chooses D1

True - I failed to allow for double character columns..

To correct, Change:

If Rng2.Address Rng1.Address _

to

If Rng2.Column Rng1.Column _

But besides the weakness in an alphabetic comparison, the defintion of
what
is the last cell remains a problem.

Agreed.

Maybe one needs to use polar coordinates. (but which is dominant, angle
or
distance).

LOL!

However, this suggests that the decision as to precedence should be
postponed to point of use with an (abrtrary) default.

With this in mind (and resolving an additional problem relating to
co-incident columns/Rows):

Function LastRangeCell(BigRng As Range, _
Optional blColHasPrecedence As Boolean = True) _
As Range

Dim Rng1 As Range, Rng2 As Range
Dim i As Long

With BigRng
Set Rng1 = .Areas(1)(.Areas(1).Cells.Count)
For i = 2 To .Areas.Count
Set Rng2 = .Areas(i)(.Areas(i).Cells.Count)
If blColHasPrecedence Then
If Rng2.Column Rng1.Column Then
Set Rng1 = Rng2
ElseIf Rng2.Column = Rng1.Column Then
If Rng2.Row Rng1.Row Then
Set Rng1 = Rng2
End If
End If
Else
If Rng2.Row Rng1.Row Then
Set Rng1 = Rng2
ElseIf Rng2.Row = Rng1.Row Then
If Rng2.Column Rng1.Column Then
Set Rng1 = Rng2
End If
End If
End If
Next
End With
Set LastRangeCell = Rng1
End Function


Sub AAA()
Debug.Print LastRangeCell(Selection, True).Address
Debug.Print LastRangeCell(Selection, False).Address
End Sub

---
Regards,
Norman

Tom,

I see now that your code succinctly (as always) accomplished what I was
trying for, with the one tweak of adding rows and columns together (below).

From studying Norman's and your posts, I learned a couple of new things -
the "set rng = ar(ar.count)" syntax is now clear to me, among other things.
Thanks for all you contribute to this group's knowledge.

Sub AAA2()
Dim lowerRight As Range
Dim ar As Range
Dim rng As Range

Set lowerRight = Range("A1")
For Each ar In Selection.Areas
Set rng = ar(ar.Count)
If rng.Row + rng.Column lowerRight.Row + lowerRight.Column Then
Set lowerRight = rng
End If
Next
lowerRight.Activate
End Sub

Doug Glancy

"Tom Ogilvy" wrote in message
...
Doug certainly points out two of the major weaknesses in trying to solve
this problem. The OP definitely misses the boat by selecting a cell
outside
the original range (depending on the selection) and using areas.count
doesn't work because a multi-area selection is organized in the order the
selection was made. That can certainly be attacked with something like

Sub AAA()
Dim lowerRight As Range
Dim ar As Range
Dim rng as Range

Set lowerRight = Range("A1")
For Each ar In Selection.Areas
Set rng = ar(ar.Count)
If rng.Row = lowerRight.Row And _
rng.Column = lowerRight.Column Then
Set lowerRight = rng
End If
Next
lowerRight.Activate
End Sub



but this highlights a more basic weakness in that there is no clear
definition of what constitutes the last cell. Is it the cell farthest to
the
right or the cell in the highest numbered row. If I have B1 and A2
selected, which is the last cell. The above will end up on one extreme,
but
which would depend a lot on order of selection.


At least the OP has archived another less than perfect routine for all to
enjoy.

--
Regards,
Tom Ogilvy



"Doug Glancy" wrote in message
...
Norman,

I came up with something like yours, but it always selects the bottom
right
corner of the last area selected, i.e., if I last area I selected
("last"
temporally speaking) is above and to the left of other selections, it
still
activates it's last cell.

The only issue I have with DFFU's is that it sometimes chooses a cell
outside of any of the area, e.g., if you select A1:B3 and D1:D2 it
"squares
the corner." However, I can't come up with anything better.

Regards,

Doug Glancy

"Norman Jones" wrote in message
...
Hi DataFreakFromUtah,

In addition to Bob's response, for a multiple area selection, try:

Sub Tester()
Dim i As Long
i = Selection.Areas.Count

With Selection
.Areas(i).Cells(.Areas(i).Cells.Count).Activate
End With
End Sub


---
Regards,
Norman



"DataFreakFromUtah" wrote in message
om...
No question here, just a procedure for the archive.

Search critera: activate the last cell in a selection
select last cell in range
select last cell in selection
activate last cell in range


Sub SelectActivateLastCellInSelectedRange()
'Activates the last cell in the selected range but keeps the
'same range selected


Dim LastRow As Variant
Dim LastCol As Variant
Dim TempRow As Variant
Dim TempCol As Variant
Dim LastCell As Range
Dim A As Range


LastRow = 1
LastCol = 1
ActiveWorkbook.Activate
For Each A In Selection.Areas
TempRow = A.Range("A1").Offset(A.Rows.Count - 1, 0).Row
If TempRow LastRow Then LastRow = TempRow
TempCol = A.Range("A1").Offset(0, A.Columns.Count - 1).Column
If TempCol LastCol Then LastCol = TempCol
Next A
Set LastCell = Cells(LastRow, LastCol)
LastCell.Activate


End Sub

Here's another option too I suppose, though it uses the dreaded sendkeys :):

Sub ActiveLastCellInRange()
SendKeys ("+{TAB}")
End Sub

Doesn't work either.

Shift+Tab just tabs in reverse.

--
Regards,
Tom Ogilvy


"DataFreakFromUtah" wrote in message
om...
Here's another option too I suppose, though it uses the dreaded sendkeys
:):

Sub ActiveLastCellInRange()
SendKeys ("+{TAB}")
End Sub