[VBA Beginner] How to Automate the “Fill Handle” (AutoFill) in Excel

Environment

• Edition: Windows 10 Pro
• Version: 20H2

Background

In Excel, you often drag the small square (Fill Handle) at the bottom right of a cell to copy formulas or values continuously. I wanted to automate this operation using VBA.

In this article, I will explain how to replicate the AutoFill function using VBA code.

Solution: Using AutoFill

Excel has an AutoFill feature that automatically copies values or formulas based on the input in a cell.

While we usually use this intuitively with a mouse, the same operation is possible in VBA.

Sample Program

Range("A3:R3").Select
Selection.AutoFill Destination:=Range("A3:R5000"), Type:=xlFillDefault
Range("A1").Select

Code Explanation

• Line 1 Range("A3:R3").Select This selects the source range for the AutoFill.
• Line 2 Selection.AutoFill Destination:=Range("A3:R5000"), Type:=xlFillDefault Based on the selected range, this automatically copies the data down to the range A3:R5000. Type:=xlFillDefault specifies the standard copy behavior.
• Line 3 Range("A1").Select Finally, this moves the selection back to cell A1. This is just to make the sheet look tidy and is not mandatory.

Use Case

I often use this AutoFill process when initializing Excel sheets.

Since manually copying and pasting template files every time was troublesome, I automated it as an initialization program. Using AutoFill significantly reduces work time and is very convenient.

Summary

In VBA, you can automate the cell fill handle operation by using Selection.AutoFill.

This is a very useful technique when you want to streamline repetitive input tasks or template maintenance. Please make use of it to shorten your work time.