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

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

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
    • 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
    • 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
    • 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
    • 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
    • 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
    • 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
    • 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.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