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

Answer 2

If you want to work with the data in the worksheet (not just certain cells) then it is better to find the last row and last column and then construct your range. Here is an example

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, LastColumn As Long
    Dim rng As Range
    
    '~~> Change this to the relevant sheet name
    Set ws = Sheet1
    
    With ws
        LastRow = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).Row
        
        LastColumn = .Cells.Find(What:="*", _
                     After:=.Range("A1"), _
                     Lookat:=xlPart, _
                     LookIn:=xlFormulas, _
                     SearchOrder:=xlByColumns, _
                     SearchDirection:=xlPrevious, _
                     MatchCase:=False).Column
                     
        Set rng = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
        
        Debug.Print rng.Address
    End With
End Sub

Answer 3

Define End Range

End Range: what does that even mean? Well, I made it up.

• It's the range starting from a specified first cell to the last cell of its Current Region.
• Current Region could contain cells above and to the left of the specified first cell, but these are not included in the End Range.
• If the specified first cell is the first cell of its Current Region, then the End Range and the Current Region are the same.
• If the specified first cell is the only or the last cell in its Current Region, then it is the only cell in its End Range.
• Be aware that this behavior is different than the behavior of CTRL+SHIFT+END. It is the same e.g. when the first specified cell is contained in the only region in the worksheet.

What would I need this End Range for?

• Consider data in a range starting from cell C3 to column P where its first row contains headers. You know It is a contiguous range, so by using Set rng = Range("C3").CurrentRegion you have created a reference to the complete range. Unfortunately your data of interest starts in cell F4. Note that Set rng = Range("F4").CurrentRegion will define the same range.
• By using the function, the expression Set rng = defineEndRange(Range("F4")) will have created a reference to the range containing your data of interest.
• Of course you could calculate the last row and column using End or Find but which one will you choose if part of the data is hidden (Find with xlFormulas), or even worse, filtered (no known option so far)?

The Code

Function defineEndRange(FirstCell As Range) _
         As Range
    
    ' Validate First Cell Range.
    If FirstCell Is Nothing Then
        GoTo ProcExit
    End If
    
    ' Define Current Region.
    Dim cr As Range
    Set cr = FirstCell.CurrentRegion
    
    ' Define End Range.
    Dim er As Range
    Set er = FirstCell _
      .Resize(cr.Rows.Count + cr.Row - FirstCell.Row, _
              cr.Columns.Count + cr.Column - FirstCell.Column)
    
    ' Define result.
    Set defineEndRange = er
    
ProcExit:
    
End Function

Sub testDefineEndRange()
    Const FirstCell As String = "D5"
    Dim rng As Range
    Set rng = defineEndRange(Range(FirstCell))
    If Not rng Is Nothing Then
        Debug.Print "End Range Address: " & rng.Address(0, 0)
    Else
        Debug.Print "No Range"
    End If
End Sub

Answer 4

If there is such a thing as "normal" then the normal way of addressing the used cells to the right of A1, including A1 itself, would be this:-

Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))

The basic syntax is to define a range by its first and last cells, here perhaps Range("A1", "G1"). The term Cells(1, Columns.Count).End(xlToLeft) uses the syntax for defining a cell by its coordinates, like Cells(1, 7) for G1. Cells(1, Columns.Count) therefore defines the last cell in row 1, No. 16384, that is cell XFD1. .End(xlToLeft) instructs to move left from there to the end of the used range and return that cell. In my example that might be G1.

The code below puts the method into context.

Dim MyRowRange As Range

Set MyRowRange = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
Debug.Print MyRowRange.Address

Observe that you not only have the address of the range but the range itself, too. You can colour it yellow or you can set the value of any cell in it.

MyRowRange.Interior.Color = vbYellow
MyRowRange.Cells(3).Value = "C1"