I have never found a decent keyboard shortcut for filling a series, despite being the self-anointed king of all things keyboard. Oh sure, I could Alt+E, I, S, Enter, but it’s just not satisfying. So in the vein of selecting adjacent columns, I added a macro to my Personal.xls.
Dim lFirstBlank As Long
If TypeName(Selection) = “Range” Then
If Selection.Columns.Count = 1 Or Selection.Rows.Count = 1 Then
lFirstBlank = GetFirstBlank(Selection)
If lFirstBlank > 1 Then
If Selection.Columns.Count = 1 Then
Selection.Cells(1).Resize(lFirstBlank – 1).AutoFill _
Selection, xlFillSeries
ElseIf Selection.Rows.Count = 1 Then
Selection.Cells(1).Resize(, lFirstBlank – 1).AutoFill _
Selection, xlFillSeries
End If
End If
End If
End If
End Sub
Function GetFirstBlank(rRng As Range) As Long
Dim i As Long
i = 0
For i = 1 To rRng.Cells.Count
If IsEmpty(rRng.Cells(i)) Then
GetFirstBlank = i
Exit For
End If
Next i
End Function
It makes some assumptions that you don’t have to make when you use the mouse. Everything above the first empty cell defines the series and the entire selection is the destination. Here are some examples where I select 10 rows:
Before:
After:
The first column is pretty straight forward. In the second column, the 12 is blown away because it’s below the first empty cell. In the third column, the first cell is empty so nothing happens. In the last column, there are no empty cells, so nothing happens.
Finally, I updated my Auto_Open/Close procedures to assign a shortcut key.
Application.OnKey “^%{DOWN}”, “SelectAdjacentCol”
Application.OnKey “^%{RIGHT}”, “FillSeries”
End Sub
Sub Auto_Close()
Application.OnKey “^%{DOWN}”
Application.OnKey “^%{RIGHT}”
End Sub
The self-anointed king of all things keyboard! I thought I was the only one!
People at work are exasperated at my speed in Excel, but really, if you only use the mouse for things where there is simply no keyboard alternative, you are not being fast, you’re just being efficient. Every day I find a new keyboeard shortcut for something is an exciting day.
I was elated to find that 2007 supports us shortcut-happy folks with a “Continue typing the rest of your 2003 shortcut” feature.
Anyway, good entry. This concludes my excitement-filled comment.
I love keyboard shortcuts. I find, however, that being well versed in both input devices is the best. I think the most efficient way to do something is with what your hands are already on. If you are good with the keyboard that’s usually where you are, but not always. The most time lost is during movement from one to the other. If you find your hand on the mouse, then good knowledge of drags +shift, +ctrl, +right mouse button down, as well as the fill handle and double-click-the-cell-border navigation can save time. I’d have to say I’ve learned much of what I know here and learn something new on my daily visit. I love this series of shortcuts. Many, many thanks.
Brett
I like shortcuts and shortcodes
On Error Resume Next
With Selection
If IsArray(Selection) And (.Columns.Count = 1 Or .Rows.Count = 1) Then Range(.Cells(1), .SpecialCells(xlCellTypeBlanks).Cells(1).Offset(IIf(.Rows.Count = 1, 0, -1), IIf(.Rows.Count = 1, -1, 0))).AutoFill Selection, xlFillSeries
End With
End Sub
You may want to make sure that there’s a single area selected, tooor just work on Areas(1)????
Hans-
Damn.
For the rest of us mere mortals,
You could assign a keyboard shortcut to “Alt+E, I, S, Enter” using AutoHotkey
example: to assign Win + z (for Excel only)
#IfWinActive ahk_class XLMAIN
#z::
Send, {ALTDOWN}e{ALTUP}is{ENTER}
return
learn more here
how to use AutoHotkey to turn almost any action into a keyboard shortcut.
clipped from: Hack Attack: Turn Any Action into a Keyboard Shortcut –
http://lifehacker.com/software/hack-attack/turn-any-action-into-a-keyboard-shortcut-316589.php