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. DK,

   I mean to ask about the second bit of code.

   Brett

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

5. This helped me understand Auto_open

   http://www.dailydoseofexcel.com/archives/2004/08/18/running-macros-when-a-workbook-is-opened/

   Thanks!
   Brett

6. Why don’t you set rAdjacent = Nothing?

7. MM: Because I’m lazy.

8. 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?

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

10. CY: http://www.dailydoseofexcel.com/archives/2006/06/12/analyzing-vendor-discounts/#comment-28943

    fzz: re the mouse: you are correct. I thought about including right side columns and above and below rows, but I’ve never needed them.

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

12. 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…

13. 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?

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

15. Awesome, thanks.

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

Posting code? Use <pre> tags for VBA and <code> tags for inline.