Excel VBA AutoFill: Methods, Examples, and Fixes

In Excel VBA, use the Range.AutoFill method to extend a formula, value pattern, or formatting into a destination range. For example, with a formula in B2 and the intended fill range B2:B100, call Range("B2").AutoFill Destination:=Range("B2:B100"), Type:=xlFillDefault; the destination must include the source cell.

What AutoFill Does

AutoFill is the VBA equivalent of using Excel’s fill handle: it examines the source range and extends its contents or pattern through a larger range. It is useful when a macro needs to fill formulas down rows, continue a number or date sequence, or copy formatting across cells. The method is called on the source range, and its required Destination argument identifies the complete range to fill, including that source.

The optional Type argument tells Excel how to fill. If omitted, Excel uses its default behavior, which infers whether to copy or extend a pattern. Supplying a type makes the intended behavior clearer and can make a macro more predictable. AutoFill does not decide how many rows belong in a report; your code must first determine the destination size.

Use AutoFill in Excel Before Writing VBA

It helps to understand the manual operation that a macro will reproduce. In the Windows desktop app, enter a formula or pattern, select the cells to extend, and drag the small square at the selection’s lower-right corner. You can also double-click the fill handle to fill down beside a neighboring column of data. Double-clicking relies on adjacent data to determine the extent, so it is not a substitute for a carefully defined destination in a macro.

For a simple formula, select the source and the cells below it, then use Home > Fill > Down. The Windows shortcut is Ctrl+D. On Excel for Mac, Control+D fills down; keyboard settings and hardware can affect which modifier key is available. These manual methods are useful for checking the result before automating it, but VBA is better for repeatable tasks across changing ranges.

Basic VBA Syntax and a Working Example

The method’s general form is sourceRange.AutoFill Destination:=destinationRange, Type:=fillType. Both ranges must be on the same worksheet, and the destination needs to contain the source range. A destination that is too small, does not include the source, or has an incompatible shape can cause an error. Qualifying each range with a worksheet avoids accidentally filling the active sheet instead of the intended one.

Suppose the worksheet is named Sheet1, input values are in A2:A100, and the result should go in column B. With the formula entered in B2, =A2*2, AutoFill down will adjust the relative reference row by row. For example, if A2 contains 5, B2 returns 10; if A3 contains 7, B3 returns 14. The formula’s relative reference changes as it is filled.

Sub FillFormulaDown()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Use the worksheet that contains the source data.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Stop if there is no data row to fill.
    If lastRow < 2 Then Exit Sub

    ' Put the first formula in B2, then extend it through the data rows.
    ws.Range("B2").Formula = "=A2*2"
    ws.Range("B2").AutoFill Destination:=ws.Range("B2:B" & lastRow), _
        Type:=xlFillDefault
End Sub

This example finds the last nonempty cell in column A, so the filled range adapts when the list grows or shrinks. It assumes the data is continuous enough that the last used cell in A represents the end of the records. It also replaces any existing contents in B2 through the detected last row. Adjust the source column, formula, worksheet name, and output column to fit the workbook rather than running it unchanged on unrelated data.

Choose the Right AutoFill Type

The XlAutoFillType constants control what Excel does with the source. The default is often appropriate for formulas because it copies the formula pattern while adjusting relative references. The most useful choices include:

xlFillDefault: lets Excel infer whether to copy or extend a pattern.
xlFillCopy: copies the source contents and formatting rather than deliberately extending a series.
xlFillSeries: extends a value series, such as a numeric sequence.
xlFillFormats: fills formatting without filling the source values.
xlFillValues: fills values without carrying over formatting.
xlFillDays, xlFillWeekdays, xlFillMonths, and xlFillYears: extend date patterns according to the named interval.

For instance, with the values 10, 20, and 30 in A2:A4, using xlFillSeries with destination A2:A8 extends the step of 10, giving 40, 50, 60, 70, and 80 in A5:A8. A single starting value may not provide enough information for Excel to infer the step you intend; seed the range with representative values or use a more explicit approach when the sequence matters. Dates can also be ambiguous: filling weekdays skips weekend dates, while filling days includes them.

Filling Formulas, Multiple Columns, and Variable Ranges

When filling formulas, Excel adjusts relative references, preserves absolute references, and adjusts mixed references according to their anchored row or column. Thus, a formula that points to a fixed tax rate with an absolute reference can be copied down without moving that rate reference. Check the reference style in the source formula before filling; AutoFill follows Excel’s normal copy rules rather than interpreting the business meaning of the formula.

You can fill a block rather than a single column. If the source is A2:B3 and the destination is A2:B20, Excel extends the two-row pattern down the larger block. The destination must retain compatible dimensions and include the original source. If the task is to copy a single formula across columns instead, use a horizontal destination that includes its source cell. Inspect the first few filled rows and columns when the source contains mixed references or a multi-cell pattern.

For changing data sizes, code typically calculates a last row, as in the example, and builds the destination from that value. Other sound options include using a named range or an Excel Table with structured references. Tables often extend calculated-column formulas as rows are added, which may remove the need for a separate AutoFill macro. Avoid relying on the currently selected cell or active worksheet: explicit worksheet and range references make a procedure easier to understand and less vulnerable to a user clicking elsewhere.

AutoFill Compared with Copy and Paste

AutoFill is useful when a pattern should continue, especially when relative references or a series are involved. Direct assignment or copying may be clearer when every target cell should receive exactly the same content. For example, Destination.Value = Source.Value assigns values, but does not extend a formula pattern. Copying a range with VBA can carry formulas and formatting, while the exact outcome depends on how the copy operation is written.

Do not assume that AutoFill is always the fastest or safest method. For large data transformations, assigning a two-dimensional array to a range can avoid repeated worksheet operations, but that approach requires code to calculate each result and is less convenient for extending formatting or Excel’s built-in series behavior. Choose AutoFill when its pattern logic matches the task; choose explicit values or formulas when you need strict, easily audited outcomes.

Common Problems and How to Fix Them

Destination error: Confirm that the destination includes the complete source range and is on the same worksheet. Qualify source and destination with the same worksheet variable.
Unexpected repeated values: A single seed value may lead Excel to copy rather than infer the intended sequence. Supply multiple starting values or specify an appropriate fill type.
Formulas point to the wrong cells: Review relative, absolute, and mixed references in the seed formula. AutoFill adjusts them just as a manual fill does.
Existing data disappears: The destination is written to by the fill operation. Check the target range, or add code that asks for confirmation before overwriting important contents.
Nothing fills to the expected last row: The sample macro looks for the last used cell in column A. Blank gaps or records defined by another column may make that the wrong measure; calculate the endpoint using the column that reliably defines a record.
Macro cannot run: Confirm that the workbook is saved in a macro-enabled format and that the file is opened in desktop Excel with macros permitted by the organization’s security policy.

Run and Protect the Macro

In Windows desktop Excel, open the Visual Basic Editor with Alt+F11, insert a standard module, and place the procedure there. Run a selected procedure from the editor, or use Alt+F8 in Excel to open the Macro dialog. In Excel for Mac, VBA is available in the desktop application, but editor access and keyboard shortcuts can vary with the keyboard and system settings; Option+F11 or the Developer tab may be available. Excel for the web can open and edit many workbooks but cannot run VBA macros.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm), or the macro may not be retained in the usual macro-free .xlsx format. Before running a macro, save a separate copy of the workbook and confirm the destination range. VBA changes cannot be undone with Ctrl+Z, and macros from untrusted files can make unwanted changes. Only enable macros when the file and its source are trusted; do not lower security settings simply to run an unfamiliar procedure.

Frequently Asked Questions

How do I autofill a formula down in VBA?
Put the formula in the first cell, then call that cell’s AutoFill method with a destination range that includes it. Use xlFillDefault for Excel’s normal formula-fill behavior, and qualify both ranges with the correct worksheet.

Does the AutoFill destination include the starting cell?
Yes. The destination is the complete filled range, including the source range. If the formula starts in B2 and should reach B100, the destination is B2:B100, not B3:B100.

How do I autofill numbers in a series instead of copying them?
Use xlFillSeries and give Excel enough seed values to establish the pattern. For example, 10, 20, and 30 establish an increment of 10 more clearly than a lone starting value.

Can Excel for the web run VBA AutoFill?
No. VBA macros run in desktop Excel, not Excel for the web. The web app may let you view or edit workbook content, but you need a supported desktop version to execute the macro.

Can I undo an AutoFill macro with Ctrl+Z?
No. Excel’s undo history does not reverse changes made by a VBA macro. Save a copy first, and have the macro verify its target before it writes to cells.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.