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.

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.

---
Regards,
Norman



"Dave B" wrote in message
...
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.

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

-------------------

--
ilyaskaz
-----------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...fo&userid=2396
View this thread: http://www.excelforum.com/showthread.php?threadid=47777

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