How to Autofill Using Excel VBA

In this tutorial, you will learn how to use the Autofill feature in Excel VBA to fill a range of cells with data based on a pattern or a series. Autofill is a useful tool that can save you time and avoid errors when you need to enter repetitive or sequential data in your worksheets.

What is Autofill?

Autofill is a feature in Excel that allows you to automatically fill a range of cells with data based on the first cell or cells in the range. For example, if you enter 1 in cell A1 and 2 in cell A2, select both cells then drag the fill handle (the small square at the bottom right corner of the cell) to cell A10, Excel will autofill the range A1:A10 with the numbers 1 to 10. Similarly, if you enter Jan in cell B1 and drag the fill handle to cell B12, Excel will autofill the range B1:B12 with the months of the year.

You can also use Autofill to fill a range of cells with custom lists that you create or modify in Excel. For example, you can create a list of colors and use Autofill to fill a range of cells with those colors.

How to Use Autofill in Excel VBA

You can use the Range.Autofill method in Excel VBA to perform the same action as dragging the fill handle in Excel. The syntax of the Range.Autofill method is:

Range.AutoFill Destination:=Range, Type:=xlFillDefault

The Range argument specifies the range of cells that contains the data you want to use as the basis for autofilling. The Destination argument specifies the range of cells you want to fill with data. The Type argument specifies the type of autofilling you want to apply. The possible values for this argument are:

• xlFillDefault: Fills the destination range with data based on the default behavior of Excel. The default can fill the destination range with formulas, numbers, or text based on the source cell.
• xlFillCopy: Copies the data from the source range to the destination range without any changes.
• xlFillSeries: Fills the destination range with a series of values based on the source range.
• xlFillFormats: Copies only the formats from the source range to the destination range.
• xlFillValues: Copies only the values from the source range to the destination range.
• xlFillDays: Fills the destination range with a series of dates incremented by one day.
• xlFillWeekdays: Fills the destination range with a series of dates incremented by one weekday.
• xlFillMonths: Fills the destination range with a series of dates incremented by one month.
• xlFillYears: Fills the destination range with a series of dates incremented by one year.
• xlGrowthTrend: Fills the destination range with a growth trend or a geometric series of numbers based on the source range.
• xlLinearTrend: Fills the destination range with a linear trend based on the source range.
• xlFlashFill: Fills the destination range with data based on Flash Fill, which is an intelligent feature that analyzes your data and fills in missing values or extracts values from other columns.

Note that not all types of autofilling are applicable to all types of data. For example, you cannot use xlFillDays to fill a range of cells with text values.

Example 1: How to Autofill Using Excel VBA

Let’s see an example of how to use Autofill in Excel VBA. Suppose you have a worksheet named “Sales” containing the following data:

We want to use Autofill to fill column A with names of the months and to fill colum B with a linear trend based on the existing values column B. To do this, we can use the following VBA code:

Sub AutofillExample()

    'Activate worksheet
    Worksheets("Sales").Activate
    
    'Select source range
    Set startingRange = ActiveSheet.Range("A1:A2")
    
    'Autofill destination range with xlFillMonths
    startingRange.AutoFill Destination:=Range("A1:A12"), Type:=xlFillMonths
    
    'Select source range
    Set startingRange = ActiveSheet.Range("B1:B2")
    
    'Autofill destination range with xlLinearTrend
    startingRange.AutoFill Destination:=Range("B1:B12"), Type:=xlLinearTrend

End Sub

When you run this code, columns A and B will be filled, like this:

Code More, Distract Less: Support Our Ad-Free Site
You might have noticed we removed ads from our site - we hope this enhances your learning experience. To help sustain this, please take a look at our Ultimate VBA Training Bundle. Each purchase directly supports this site, ensuring we can continue to offer you quality, distraction-free tutorials.

Example 2: Fill a range with the same value

Suppose you have a worksheet like this:

And you want to fill column B with the value “Hello”. You can use VBA Autofill to do this in one line of code:

Sub AutofillExample2()
    Range("B1").AutoFill Destination:=Range("B1:B4"), Type:=xlFillDefault
End Sub

This code will copy the value in B1 and fill the rest of the range B1:B4 with the same value. The result will look like this:

Again, the Type argument specifies how to fill the range. In this case, we use xlFillDefault, which means to copy the value or formula in the first cell. You can also use other types, such as xlFillCopy, xlFillSeries, xlFillValues, etc.

Example 3: Fill a range with a series of values

Suppose you have a worksheet like this:

And you want to fill column B with a series of values that starts from a number that’s 5 greater than the value in Cell A1. You can use VBA Autofill to do this in two lines of code:

Sub AutofillExample3()
    Range("B1").Value = Range("A1") + 5
    Range("B1").AutoFill Destination:=Range("B1:B4"), Type:=xlFillSeries
End Sub

This code will first assign the value in A1 plus 5 to B1, and then fill the rest of the range B1:B4 with a series of values that increases by 1. The result will look like this:

Example 4: Fill a range with formulas

Suppose you have a worksheet like this:

And you want to fill column B with formulas that calculate f(x) = -0.0625x^2 + x + 0.5. You can use VBA Autofill to do this in two lines of code:

Sub AutofillExample4()
    Range("B2").Formula = "=-0.0625*A2^2+A2+0.5"
    Range("B2").AutoFill Destination:=Range("B2:B5"), Type:=xlFillCopy
End Sub

This code will first assign the formula to B2, and then fill the rest of the range B2:B5 with the same formula. The result will look like this:

In this example, you would have gotten the same result if you used the xlFillSeries or the xlFillDefault Type. That just goes to show you that there are often multiple Type parameters that can be used to accomplish the same VBA AutoFill behavior.

I hope this tutorial was helpful and you learned how to use AutoFill in VBA to fill a range with different types of series. Thank you for reading!