Activate last cell in selected range - an example

  • Thread starter Thread starter DataFreakFromUtah
  • Start date Start date
D

DataFreakFromUtah

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

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
 
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.
 
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).
 
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
 
Hi Tom,
If B1 and AA1 are selected, this chooses B1

if B10, D1, AA1 are selected this chooses D1
Click to expand...

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

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
 
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
 
Here's another option too I suppose, though it uses the dreaded sendkeys :):

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