Select Adjacent

I’ve never found a decent keyboard combination for selecting a single-column range whose length is determined by an adjacent column. If I double-click the fill handle, for example, it will fill a formula down as far as the column on the left has data. That’s nice when you have a lot of data.

I usually use Shift+PageDown to select what I need, then Cntl+D to fill down the formula. Well no more! Now I’m using this macro:

Sub SelectAdjacentCol()
   
    Dim rAdjacent As Range
   
    If TypeName(Selection) = “Range” Then
        If Selection.Column > 1 Then
            If Selection.Cells.Count = 1 Then
                If Not IsEmpty(Selection.Offset(0, -1).Value) Then
                    With Selection.Offset(0, -1)
                        Set rAdjacent = .Parent.Range(.Cells(1), .End(xlDown))
                    End With
                   
                    Selection.Resize(rAdjacent.Cells.Count).Select
                End If
            End If
        End If
    End If
   
End Sub

Make sure it’s a range, make sure it’s column B or greater, make sure there’s only one cell selected, and finally make sure there’s something in the column to the left. I’m using the key combination Cntl+Alt+DownArrow. Here’s how I set that up:

Sub Auto_Open()
   
    Application.OnKey “^%{DOWN}”, “SelectAdjacentCol”
   
End Sub
 
Sub Auto_Close()
   
    Application.OnKey “^%{DOWN}”
   
End Sub
Posted in Uncategorized

16 thoughts on “Select Adjacent

  1. Fantastic! I have been grumbling about not being able to do this for ages.

    The Ctrl+C, Left-arrow, Ctrl+Down, Right-arrow, Shift+Ctrl+Up keyboard combo was beginning to get a bit tired, so this is a fantastic addition.

  2. Dick,

    For non-VBA: I have the Transition Navigation Keys checked in Tools=>Options=>tab Transition. Then, referring to your graphic, I i)Select B1 ii)Hold Shift and Arrow Left to A1 iii) Hold Shift and press End then Arrow Down iv) Arrow right. At this point the selection should be the length of the column adjacent to it. Then either Control+D if you want all characteristics of the cell copied, or F2 then Control+Enter if you just want the formula.
    I have become addicted to VBA and am going to explore your example. Does the code go in an add-in’s workbook object?

    Brett

  3. Answered my own question and noticed that I typed B1 and should have typed B2 in my first comment. Also, Ctrl+Alt+Arrow rotates my screen. I replaced the “%” with “+” so the shortcut is Ctrl+Shift+Down Arrow. Man, is this FUN!

    Brett

  4. Dick:

    I found your article on vendor discounts very interesting and useful. Can you explain to a math dummy how you arrive to the 36% savings realized by taking advantage of the 2% available discount?

  5. I suppose double-clicking the fill handle doesn’t count as simple since it involves the mouse.

    FWIW,

    ^C
    +{left}
    {tab}
    +^{down}
    {tab}
    +{right}
    ^V

    Also, FWIW, why not allow the adjacent range to be a column to the right, rows above or below, or both rows and columns bounding a region? Something like

    Sub foo()
      Dim a As Range, r As Range
      Dim i As Long, j As Long

      If Not TypeOf Selection Is Range Then Exit Sub
      If Selection.Cells.Count > 1 Then Exit Sub

      Set a = ActiveCell
      Set r = a.CurrentRegion

      With Application.WorksheetFunction
        i = .CountA(Intersect(r, a.EntireColumn))
        j = .CountA(Intersect(r, a.EntireRow))
      End With

      If i > j Then
        Intersect(r, _
         a.EntireRow.Offset(0, a.Column – r.Column)).Select
      ElseIf j > i Then
        Intersect(r, a.EntireColumn.Offset(a.Row – r.Row, 0)).Select
      Else
        Intersect(r, _
         r.Offset(a.Row – r.Row, a.Column – r.Column)).Select
      End If
    End Sub

  6. Using the original sub as a starting point, I fumbled my way thru VBA to create a modification that allows the selection can be anywhere to the right or left of the column to be operated upon. Simply put, it calculates the required offset, then selects the range. Seems to work OK, but does anyone see any pitfalls? I have no need for a keyboard shortcut, but I do have the need to do formula fill-downs. However, if some of the cells in the target range are not populated, then it stops. In that event I have another sub (ActivateWhatever — not sure where I got it from) that deals with this. Of course sometimes it goes far beyond the range of interest,

    Sub SelectTargetCol()
    Dim rAdjacent As Range
    Dim SetOff As Integer
    Set TRange = Application.InputBox(Prompt:=”WhichColumn?”, Type:=8)
    SetOff = ActiveCell.Column – TRange.Column
    If TypeName(Selection) = “Range” Then
    If Selection.Cells.Count = 1 Then
    If Not IsEmpty(Selection.Offset(0, -SetOff).Value) Then
    With Selection.Offset(0, -SetOff)
    Set rAdjacent = .Parent.Range(.Cells(1), .End(xlDown))
    End With
    Selection.Resize(rAdjacent.Cells.Count).Select
    End If
    End If
    End If
    End Sub

    Sub ActivateWherever()
    ‘this identifies the last used row anywhere on the sheet & allows you to specify where selection starts
    Dim Last As Double, Where As Double
    Where = InputBox(Prompt:=”Which row?”)
    Last = Cells.Find(What:=”*”, After:=[a1], _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    Range((Cells(Where, ActiveCell.Column)), Cells(Last, ActiveCell.Column)).Select
    End Sub

  7. The easiest way to select cells B2:B19 is to do the following:

    Go to A2
    CTRL-DOWN
    RIGHT
    CTRL-SHIFT-UP

    Use it all the time. It always freaks people out who watch you when you do it quickly…

  8. I’ve pasted all three pieces of code in a standard module, saved and reopened the file. Now when I reopen the file and use the shortcut, it only selects the cells and does not fill the formula. Can you help me what might be causing troubles.

    Secondly, if I want this to be available in all the excel files do I just paste the code in Personal.xls?

  9. Dion: It doesn’t fill the formula down, it just selects the cells. After the cells are selected, press Ctl+D to fill down. Or if you want to adjust the macro, after the Selection.Resize line, put

    Selection.FillDown

    Yes, Personal.xls is where I have mine.

  10. This version extends the range to the longer of the column immediately left or right. It also makes sure there’s a cell directly below the cell to the left or right to make sure the xlDown doesn’t return the last row in the worksheet when it’s blank.

    The second version does the same with rows, extending them rightward.

    Sub SelectAdjacentCol()
    Dim lRows As Long
       
        On Error Resume Next
           
        If Not IsEmpty(Selection.Offset(0, -1)) And Not IsEmpty(Selection.Offset(1, -1)) Then _
            lRows = Range(Selection.Offset(0, -1), Selection.Offset(0, -1).End(xlDown)).Cells.Count
        If Not IsEmpty(Selection.Offset(0, 1)) And Not IsEmpty(Selection.Offset(1, 1)) Then _
            lRows = Application.WorksheetFunction.Max(lRows, Range(Selection.Offset(0, 1), Selection.Offset(0, 1).End(xlDown)).Cells.Count)

        Selection.Resize(lRows).Select
           
    End Sub

    Sub SelectAdjacentRow()
    Dim lRows As Long
       
        On Error Resume Next
           
        If Not IsEmpty(Selection.Offset(-1, 0)) And Not IsEmpty(Selection.Offset(-1, 1)) Then _
            lRows = Range(Selection.Offset(-1, 0), Selection.Offset(-1, 0).End(xlToRight)).Cells.Count
        If Not IsEmpty(Selection.Offset(1, 0)) And Not IsEmpty(Selection.Offset(1, 1)) Then _
            lRows = Application.WorksheetFunction.Max(lRows, Range(Selection.Offset(1, 0), Selection.Offset(1, 0).End(xlToRight)).Cells.Count)

        Selection.Resize(1, lRows).Select
       
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *