Using SendKeys to select cells

Question

In an Excel worksheet, selecting cell A1 and then using the key combination Ctrl+Shift+End will select the range of the worksheet with data. Using the VBA statements

Range("A1").Select
Sendkeys "^+{END}"

highlights the range. However, there does not seem to be anyway of retrieving the address of the selected region. VBA does not seem to recognize that a range has been selected. Suggestions about how to return the selected range would be appreciated.

Answer 1

I assume that you are aware that you don't need to use SendKeys to mimick Ctrl+Shift+End.

You can use the CurrentRegion command to either select or identify the extent of the range.

Sub test()

Range("A1").CurrentRegion.Select
' next you could do something with Selection.Address

' just for illustration, you don't need to select if your goal
' is to return the address of the range.
' here I'm using debug.print only to have a container for the 
' returned range address without selecting the range first

Debug.Print Range("A1").CurrentRegion.Address

End Sub