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
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
Application.OnKey "^%{DOWN}", "SelectAdjacentCol"
End Sub
Sub Auto_Close()
Application.OnKey "^%{DOWN}"
End Sub
Andy Cotgreave:
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.
26 November 2007, 5:48 amBrett:
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
26 November 2007, 7:39 amBrett:
DK,
I mean to ask about the second bit of code.
Brett
26 November 2007, 7:46 amBrett:
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
26 November 2007, 8:15 amBrett:
This helped me understand Auto_open
http://www.dailydoseofexcel.com/archives/2004/08/18/running-macros-when-a-workbook-is-opened/
Thanks!
26 November 2007, 8:22 amBrett
MacroMan:
Why don't you set rAdjacent = Nothing?
26 November 2007, 9:24 amDick Kusleika:
MM: Because I'm lazy.
26 November 2007, 9:55 amChris Youngblood:
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?
26 November 2007, 11:03 amfzz:
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
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
Dick Kusleika:
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.
26 November 2007, 2:51 pmJerry Gluck:
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()
28 November 2007, 6:18 pm'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
Dan:
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...
16 December 2007, 3:25 pmDion:
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?
31 December 2007, 11:58 amDick Kusleika:
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.FillDownYes, Personal.xls is where I have mine.
1 January 2008, 10:06 amDion:
Awesome, thanks.
1 January 2008, 10:13 pm