Tags: activate, archive, cell, critera, example, excel, microsoft, msdn, procedure, range, rangeselect, search, selected, selectionselect, software
Activate last cell in selected range - an example
On Microsoft » Microsoft Excel
16,944 words with 9 Comments; publish: Wed, 28 May 2008 21:04:00 GMT; (30678.13, « »)
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
http://excel.itags.org/q_microsoft-excel_9515.html
All Comments
Leave a comment...
- 9 Comments
- Or, you can use:
Sub SelectActivateLastCellInSelectedRange()
Selection.Cells(Selection.Cells.Count).Select
End Sub
Bob Umlas
Excel MVP
"DataFreakFromUtah" <aztecbrainsurgeon.excel.itags.org.yahoo.com> wrote in message
news:b43cbc23.0409011501.50be9c13.excel.itags.org.posting.google.c 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
#1; Wed, 28 May 2008 21:05:00 GMT
- 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" <aztecbrainsurgeon.excel.itags.org.yahoo.com> wrote in message
news:b43cbc23.0409011501.50be9c13.excel.itags.org.posting.google.c 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
#2; Wed, 28 May 2008 21:06:00 GMT
- 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" <normanjones.excel.itags.org.whereforartthou.com> wrote in message
news:uh3E97HkEHA.556.excel.itags.org.tk2msftngp13.phx.gbl...
> 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" <aztecbrainsurgeon.excel.itags.org.yahoo.com> wrote in message
> news:b43cbc23.0409011501.50be9c13.excel.itags.org.posting.google.c om...
>
#3; Wed, 28 May 2008 21:07:00 GMT
- 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" <nobodyhere.excel.itags.org.replytogroup.com> wrote in message
news:eSdMbfIkEHA.2812.excel.itags.org.tk2msftngp13.phx.gbl...
> 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" <normanjones.excel.itags.org.whereforartthou.com> wrote in message
> news:uh3E97HkEHA.556.excel.itags.org.tk2msftngp13.phx.gbl...
>
#4; Wed, 28 May 2008 21:08:00 GMT
- 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" <nobodyhere.excel.itags.org.replytogroup.com> wrote in message
news:eSdMbfIkEHA.2812.excel.itags.org.tk2msftngp13.phx.gbl...
> 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" <normanjones.excel.itags.org.whereforartthou.com> wrote in message
> news:uh3E97HkEHA.556.excel.itags.org.tk2msftngp13.phx.gbl...
>
#5; Wed, 28 May 2008 21:09:00 GMT
- 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" <normanjones.excel.itags.org.whereforartthou.com> wrote in message
news:uF%237siJkEHA.3648.excel.itags.org.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi Doug,
>
> Ageed
>
> 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" <nobodyhere.excel.itags.org.replytogroup.com> wrote in message
> news:eSdMbfIkEHA.2812.excel.itags.org.tk2msftngp13.phx.gbl...
("last"
>
#6; Wed, 28 May 2008 21:10:00 GMT
- 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" <aztecbrainsurgeon.excel.itags.org.yahoo.com> wrote in message
news:b43cbc23.0409011501.50be9c13.excel.itags.org.posting.google.c 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
#7; Wed, 28 May 2008 21:11:00 GMT
- 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
#8; Wed, 28 May 2008 21:12:00 GMT
- 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" <twogilvy.excel.itags.org.msn.com> wrote in message
news:eWq4ttJkEHA.2948.excel.itags.org.TK2MSFTNGP11.phx.gbl...
> 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[vbcol=seagreen]
> 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" <nobodyhere.excel.itags.org.replytogroup.com> wrote in message
> news:eSdMbfIkEHA.2812.excel.itags.org.tk2msftngp13.phx.gbl...
> right
("last"
> still
> "squares
>
#9; Wed, 28 May 2008 21:13:00 GMT