Learning to use VBA to run Excel's AutoFill command lets you create a commonly-occurring numeric or text series with a single mouse click. This saves you from having to type the initial values that AutoFill needs, and drag the mouse to fill the series. Use VBA AutoFill to rapidly number a list of items, or to specify each date in a range of dates. To ensure your VBA AutoFill programs perform as expected, always explicitly specify the function's optional second argument.
Record a Macro
You don't have to understand any VBA statements to create a VBA AutoFill program. Excel's macro recorder can create an AutoFill program for you, based on your manual usage of AutoFill. Type the numbers 1 and 2 in two successive rows of an Excel spreadsheet, then select the two cells by dragging over them. Click the "Developer" tab's "Record" command, then type a name for the macro. Hover your mouse over the lower right corner of the bottom cell in the selection. When the cursor turns to a large plus symbol, drag directly downward to run AutoFill, which will extend the series beginning with the numbers 1 and 2 that you typed. click the "Developer" tab's "Stop" button to end the macro recording.
Study AutoFill Macro Statements
Once you've recorded a VBA macro that converts your manual usage of AutoFill into VBA statements, study those statements to begin understanding how to run AutoFill from VBA. Click the "Developer" tab's "Macros" button to display a list of macros. Click the name of your AutoFill macro and click "Edit" to make Excel display your program's statements within the development environment. If the only action you took while recording the macro was to use AutoFill to extend the numeric series starting with 1 and 2, your program will look like the one following this section.
Selection.AutoFill Destination:=Range("I5:I15"), Type:=xlFillDefault
The AutoFill function is a member of an Excel "Range" object, which represents a collection of worksheet cells. The "Selection" object is a special kind of range, representing cells you've highlighted with the mouse or keyboard. The only argument the AutoFill function needs to work is the first one: "Destination." This argument specifies both the source of the series with which you want to fill and the destination. The destination is the range of cells in which Excel creates additional values in the series started by the source.
Create an AutoFill Program Manually
You can create a VBA AutoFill program manually rather than with macro recording. To do so, enter the Visual Basic development environment by clicking the "Developer" tab's "Visual Basic" button, then paste the following program into the development window. This program inserts two day names, "Monday" and "Tuesday," into the current worksheet. It then uses the VBA AutoFill command to extend the series implied by those two days. The program uses the AutoFill second argument, "Type," to tell the AutoFill function to fill with weekdays instead of another type of value like integers. Run the program by clicking the "Run" menu's "Run" command. When you return to Excel, you'll see the days "Monday" through "Thursday" in the current worksheet's cells.
Range("L6") = "Monday"
Range("L7") = "Tuesday"
Selection.AutoFill Destination:=Range("L6:L9"), Type:=xlFillDays
- "Excel 2010 Bible"; John Walkenbach; 2010
- "Mastering VBA for Office 2010"; Richard Mansfield; 2010