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.

[ilyaskazi]

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

Through vba, try this...

Please Login or Register  to view this content.

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

---
Regards,
Norman



"Dave B" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
>

[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

[HarveyParis]

ilyaskazi's anwer is just fine. Thanks a lot.
The others too. Just replace ".Select" by ".Address" to have the cell.

MsgBox (ActiveCell.SpecialCells(xlCellTypeLastCell).Address)
MsgBox (ActiveCell.SpecialCells(xlLastCell).Address)

And if you want only the row
MsgBox ("La ligne active est : " & ActiveCell.SpecialCells(xlLastCell).Row)
Usefull when you want to make a loop until "end of file".