Using VBA to Select "Corner Cell" in Excel.. Cell in last row and last column...

[ArbeitWali]

Trying to have VBA Select "Corner Cell" in Excel..
Cell in last row and last column...


So I'm starting from a point where the region is already selected.... the region having just been pasted.

 

[Nebu]

Hi:

Go through this link.

http://www.rondebruin.nl/win/s9/win005.htm

Thanks

 

[ArbeitWali]

Thanks much my friend.. Checking it now..

Best

 

[shrivallabha]

You can use something like below:

Sub BottomRightCell()
Dim rngBtmRgt As Range
Set rngBtmRgt = Selection.Cells(Selection.Rows.Count, Selection.Columns.Count)
MsgBox rngBtmRgt.Address
End Sub

 

[jindon]

This should do.

MsgBox Selection(Selection.Count).Address

 

[YasserKhalil]

Thanks everyone for these great codes
What about this

Sub Test()
    MsgBox Selection.SpecialCells(xlCellTypeLastCell).Address
End Sub

 

[Deepak]

Thanks everyone for these great codes
What about this

Sub Test()
    MsgBox Selection.SpecialCells(xlCellTypeLastCell).Address
End Sub

The output became wrong when selection ranges are empty!

 

[YasserKhalil]

I tested it with empty range and no errors ..what is the expected error?

 

[Deepak]

I tested it with empty range and no errors ..what is the expected error?

Check this..

 

[YasserKhalil]

That's weird ...
I have tested it on colored selection and it worked .. the range is empty but it has interior color ...So it will not deal with the range (selection) if it is clear at all

 

[ArbeitWali]

Hi Folks,

Thanks all for these great and helpful codes...

 

[shrivallabha]

That's weird ...
I have tested it on colored selection and it worked .. the range is empty but it has interior color ...So it will not deal with the range (selection) if it is clear at all
View attachment 29195

What result do you get if you type something in I21?

xlCellTypeLastCell returns the last cell in the sheet which is bottom-right corner of usedrange so it will invariably give wrong results than right.

 

[YasserKhalil]

You are right. It seems that I can't assign last cell for the selection in this way
Thanks a lot