Ctrl+End with VBA

[Dave B]

How can I use VBA to find out which cell Excel would move to if I pressed
Ctrl+End (i.e. how do I find the last cell in the used range using VBA)?
Thanks.

 

[Norman Jones]

Hi Dave B,

how do I find the last cell in the used range using VBA)?

Try:

Dim rng As Range

Set rng = Cells.SpecialCells(xlCellTypeLastCell)

Note that the last cell in the used range may not be populated.

 

[ilyaskazi]

By pressing CTRL + End key excel takes you to last used range of you
active sheet.

Through vba, try this...


Code
-------------------

Sub Select_CTRL_END()
Dim myCol As Integer, myRow As Long
myCol = ActiveSheet.UsedRange.Columns.count
myRow = ActiveSheet.UsedRange.Rows.count

ActiveSheet.Cells(myRow, myCol).Select 'optional to select

'Find your last cell
MsgBox ActiveSheet.Cells(myRow, myCol).Address(False, False)

End Sub

 

[Paul D. Simon]

I believe this 1-line code should do what you're looking for.

ActiveCell.SpecialCells(xlLastCell).Select


or as an alternative, this 1 line should work also.

ActiveCell.SpecialCells(xlCellTypeLastCell).Select