FillSeries Keyboard Shortcut
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
