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.

Sub FillSeries()
   
    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.

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

5 thoughts on “FillSeries Keyboard Shortcut

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

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

  3. I like shortcuts and shortcodes

    Sub FillSeries()
      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
  4. You may want to make sure that there’s a single area selected, too–or just work on Areas(1)????

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

Leave a Reply

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

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax