### 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; (30662.50, « »)

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.todaysummary.com/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.todaysummary.com.yahoo.com> wrote in message

news:b43cbc23.0409011501.50be9c13.excel.todaysummary.com.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

- Or, you can use:
- 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.todaysummary.com.yahoo.com> wrote in message

news:b43cbc23.0409011501.50be9c13.excel.todaysummary.com.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

- Hi DataFreakFromUtah,
- 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.todaysummary.com.whereforartthou.com> wrote in message

news:uh3E97HkEHA.556.excel.todaysummary.com.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.todaysummary.com.yahoo.com> wrote in message

> news:b43cbc23.0409011501.50be9c13.excel.todaysummary.com.posting.google.c om...

>

#3; Wed, 28 May 2008 21:07:00 GMT

- Norman,
- 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.todaysummary.com.replytogroup.com> wrote in message

news:eSdMbfIkEHA.2812.excel.todaysummary.com.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.todaysummary.com.whereforartthou.com> wrote in message

> news:uh3E97HkEHA.556.excel.todaysummary.com.tk2msftngp13.phx.gbl...

>

#4; Wed, 28 May 2008 21:08:00 GMT

- Hi Doug,
- 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.todaysummary.com.replytogroup.com> wrote in message

news:eSdMbfIkEHA.2812.excel.todaysummary.com.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.todaysummary.com.whereforartthou.com> wrote in message

> news:uh3E97HkEHA.556.excel.todaysummary.com.tk2msftngp13.phx.gbl...

>

#5; Wed, 28 May 2008 21:09:00 GMT

- Doug certainly points out two of the major weaknesses in trying to solve
- 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.todaysummary.com.whereforartthou.com> wrote in message

news:uF%237siJkEHA.3648.excel.todaysummary.com.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.todaysummary.com.replytogroup.com> wrote in message

> news:eSdMbfIkEHA.2812.excel.todaysummary.com.tk2msftngp13.phx.gbl...

("last"

>

#6; Wed, 28 May 2008 21:10:00 GMT

- If B1 and AA1 are selected, this chooses B1
- 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.todaysummary.com.yahoo.com> wrote in message

news:b43cbc23.0409011501.50be9c13.excel.todaysummary.com.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

- So here's my stab at it. I've assumed that the definition of the last cell
- 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

- Hi Tom,
- 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.todaysummary.com.msn.com> wrote in message

news:eWq4ttJkEHA.2948.excel.todaysummary.com.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.todaysummary.com.replytogroup.com> wrote in message

> news:eSdMbfIkEHA.2812.excel.todaysummary.com.tk2msftngp13.phx.gbl...

> right

("last"

> still

> "squares

>

#9; Wed, 28 May 2008 21:13:00 GMT

- Tom,