Autofill KeyboardShortcut?

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Is there a keyboard shortcut to do autofill?

I want to be able to use the keyboard + arrow keys instead
of using the mouse to click the handle at the bottom right
of a selection and drag.

Thanks,
Andy
 
No shortcut but:

In the column you are going to fill, move to the last (or
bottom) location and enter any character.

Move to the cell that contains the value you want to fill
down with and press:

[Shift] + [Ctrl] + [Down Arrow] to highlight the area and

[Ctrl] + [D] to fill down the value
 
You could write a macro and assign a keyboard shortcut to it. Here's one attempt. It may be slow
with a large range because of the rather primitive method (the For/Next loop) I used to prevent
overwriting data.

Option Explicit

Sub ClickFillHandle()
Dim AdditionalRows As Long
Dim ColOffset As Long
Dim LastRow As Long
Dim RangeToFill As Range
Dim SelRows As Long

With Selection
With .Cells(1)
If IsEmpty(.Offset(0, -1).Value) = False Then
ColOffset = -1
ElseIf IsEmpty(.Offset(0, 1).Value) = False Then
ColOffset = 1
Else
ColOffset = 0
End If
End With

If ColOffset Then
SelRows = .Rows.Count
LastRow = .Cells(1).Offset(0, ColOffset).End(xlDown).Row
AdditionalRows = LastRow - .Row + 1 - SelRows

'set variable for proposed fill range
Set RangeToFill = .Offset(SelRows, 0).Resize(AdditionalRows)

'if it's not empty, decrease its size by removing
'rows at the bottom until it is empty, then AutoFill it

For AdditionalRows = AdditionalRows To 1 Step -1
If Application.CountA(RangeToFill.Resize(AdditionalRows)) = 0 Then
.AutoFill .Resize(RowSize:=SelRows + AdditionalRows), xlFillDefault
Exit Sub
End If
Next AdditionalRows
End If
End With
End Sub
 
Of course that method doesn't mimic clicking the fill handle: it just copies the data in the top
row of the selection to the remaining cells in the selection.

If you click the fill handle, the result may be the equivalent of Edit/Fill/Series or a simple
copy, depending on the source data.

OTOH, maybe a simply copy-down is all that he really wants.
 
Back
Top