How to Use VBA AutoFill in Excel (11 Examples)

Using autofill in Excel with VBA is a great tool to use while working with data that is repetitive or in a sequential pattern. Excel gives us several options to fill up the cells or rows with data automatically like months, years, series of numbers, cell formatting and even separating a text into columns by detecting the pattern of previous actions.


Download the Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to Range.Autofill Method in Excel

Outcome:
Autofill a specified range of cells based on conditions.

Syntax:
expression.Autofill(Destination, Type)

Arguments:
Expression- A variable that represents a cell, a row, a column, a selection of cells containing one or more adjacent blocks of cells (Range Object)

Argument Required/Optional Datatype
Destination Required Range– cells need to fill. The destination range must contain the source range.
Type Optional XlAutoFillType– specifies the type of Autofill.

There are different types of XlAutoFillType arguments. 

Name Value Description
xlFillCopy 1 copy values and formats from source range to the target range, repetition allowed.
xlFillDays 5 extends the name of days of the week from source range to target range with formatting, repetition allowed.
xlFillDefault 0 Excel determines the values and formats and fills the target range from the source range.
xlFillFormats 3 copies only the formats, repetition allowed.
xlFillMonths 7 extends names of months to the target range with formats of cells, repetition allowed.
xlFillSeries 2 extends values as a series, formats are also copied from source range to target range, repeating if necessary.
xlFillValues 4 only copy the values, repeating if necessary.
xlFillWeekdays 6 extends names of the days of a workweek, formats are also copied from source range to target range, repeating if necessary.
xlFillYears 8 extends years to the target cells with the formatting of source range, repeating if necessary.
xlGrowthTrend 10 extends numeric values from source range to target range assuming that the relationship among the numbers of source range is multiplicative.
xlLinearTrend 9 extends numeric values from source range to target range assuming that the relationship among the numbers of source range is additive.
xlFlashFill 11 extends values based on the previous user actions to targeted cells from source cells.

11 Easy Examples of Using VBA AutoFill in Excel

In this article, we’ll illustrate examples for every variation of XLAutoFillType. Let’s begin!

1. Copy Information Down the Cells Using xlFillCopy

How Code Works:
Let’s explain how the code for Autofill works in the following example.

Dataset: In Column B, cells B4 and B5 contain numbers 1 and 2. The cells are also formatted with color and border.

Excel VBA Autofill

Code: Write the code in the visual basic editor.

Sub autofill()
Set SourceRange = Worksheets("xlFillCopy").Range("B4:B5")
Set TargetRange = Worksheets("xlFillCopy").Range("B4:B14")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillCopy
End Sub
Explanation:

Source Range: Cells B4 and B5 contain the values and formats to be copied to the targeted cells. We set a variable named SourceRange to hold the values and formats of these two cells.

Target Range: Cells B4:B14 in column B that need to be filled. The target range also consists of the source range. We defined a variable named TargetRange to declare the target cells.

Autofill Type: xlFillCopy that will copy values and formats from source range to the target range, repetition is allowed if necessary.

Worksheets: Name of the worksheet. In this example, the name of the worksheet is xlFillCopy (see the screenshot below)

Result: Cells from B4 to B14 get auto filled with the values and formats of cells B4:B5. Here we also see that repetition happened through the targeted cells.

Excel VBA Autofill xlFillCopy


 2. Use of xlFillDays in Excel VBA to AutoFill Names of the Week

Dataset: In column B cells B3:B4 contain Sunday and Monday, two weekdays.

Excel VBA Autofill xlFillDays

Code:

Sub autofill()
Set SourceRange = Worksheets("xlFillDays").Range("B3:B4")
Set TargetRange = Worksheets("xlFillDays").Range("B3:B11")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillDays
End Sub

Source Range: Cells B3:B4 in column B

Target Range: Cells B3:B11 in column B

Worksheet: xlFillDays

Type: xlFillDays

Result: We see the names of 7 days of a week in the target ranges along with the formatting of the source range.


3. Autofill Cells with the Default AutoFill Type of Excel VBA- xlFillDefault

Dataset: In column B cells B3:B4 contain numbers 1 and 3.

Excel VBA Autofill

Code:

Sub autofill()
Set SourceRange = Worksheets("xlFillDefault").Range("B3:B5")
Set TargetRange = Worksheets("xlFillDefault").Range("B3:B11")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillDefault
End Sub

Source Range: Cells B3:B4 in column B.

Target Range: Cells B3:B11 in column B.

Worksheet: xlFillDefault

Type: xlFillDefault

Result:  Excel determines the values and format pattern from the source range and filled the target range with a series of numbers starting from 1 with an interval of 1 among them that is 1, 3, 5, 7, 9, 11, 13

Excel VBA Autofill xlFillDefault


4. Copy Formats to Target Range Using xlFillFormats

Dataset: In column B cells B3:B5 are formatted with color (Green, Blue, and White) and border.

Excel VBA Autofill xlFillDefault

Code:

Sub autofill ()
Set SourceRange = Worksheets("xlFillFormats").Range("B3:B5")
Set fillRange = Worksheets("xlFillFormats").Range("B3:B11")
SourceRange.autofill Destination:=fillRange, Type:=xlFillFormats
End Sub

Source Range: Cells B3:B5 in column B

Target Range: Cells B3:B11 in column B

Worksheet: xlFillFormats

Result: The formats from the source range have been copied and auto-filled with repetition.

Excel VBA Autofill xlFillDefault


5. Try Out xlFillMonths to AutoFill Months of the Year in Excel

Dataset: In column B cells B3:B5 contain months Jan, Feb, and Mar.

Excel VBA Autofill

Code:

Sub autofill ()
Set SourceRange = Worksheets("xlFillMonths").Range("B3:B5")
Set TargetRange = Worksheets("xlFillMonths").Range("B3:B14")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillMonths
End Sub

Source Range: Cells B3:B5 in column B

Target Range: Cells B3:B14 in column B

Worksheet: xlFillMonths

Type: xlFillMonths

Result: The names of 12 months of a year in the target range.

Excel VBA Autofill xlFillMonths


6. Get a Series Using xlFillSeries AutoFill Type

Dataset: The cells B3:B5 of column B contain three numbers 1, 4, and 7.

Excel VBA Autofill xlFillSeries

Code:

Sub autofill()
Set SourceRange = Worksheets("xlFillSeries").Range("B3:B5")
Set TargetRange = Worksheets("xlFillSeries").Range("B3:B10")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillSeries
End Sub

Source Range: Cells B3:B5 in column B

Target Range: Cells B3:B10 in column B

Worksheet: xlFillSeries

Type: xlFillSeries

Result: Excel determines the values and format pattern from the source range and filled the targeted range with a series of numbers starting from 1 with an interval of 2 among them that is 1, 4, 7, 10, 13, 16, 19, 22.


7. AutoFill Values in Targeted Cells Using xlFillValues

Dataset: B3:B5 cells of column B store values 1, 2, and 3. These cells are formatted with color, text centering, and border.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlFillValues").Range("B3:B5")
Set TargetRange = Worksheets("xlFillValues").Range("B3:B10")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillValues
End Sub

Source Range: cells B3:B5 in column B

Target Range: cells B3:B10 in column B

Worksheet: xlFillValues

Types: xlFillValues

Result: Running the code result in auto-filling only the values in the target cells. But the formats of the source range weren’t carried to the target range.

Excel VBA Autofill xlFillValues


8. Get a Range of Years Using Excel VBA with xlFillYears 

Dataset: B3:B4 cells of column B store two dates 1/1/2000 and 1/1/2001.

Excel VBA Autofill xlFillYears

Code:

Sub autofill()
Set SourceRange = Worksheets("xlFillYears").Range("B3:B4")
Set TargetRange = Worksheets("xlFillYears").Range("B3:B10")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillYears
End Sub

Source Range: cells B3:B4 in column B

Target Range: cells B3:B10 in column B

Worksheet: xlFillYears

Type: xlFillYears

Result: In the following cells of the source range, we see an increment of years while the day and the month remained the same.

Excel VBA Autofill xlFillYears


9. AutoFill Type- xlGrowthTrend 

Dataset: In column B, cells B3:B4 contain numbers 1 and 2.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlGrowthTrend").Range("B3:B4")
Set TargetRange = Worksheets("xlGrowthTrend").Range("B3:B10")
SourceRange.autofill Destination:= TargetRange, Type:=xlGrowthTrend
End Sub

Source Range: Cells B3:B4 in column B

Target Range: Cells B3:B10 in column B

Worksheet: xlGrowthTrend

Types: xlGrowthTrend

Result: Excel assumes the relationship as multiplicative so 1, 2 is extended as 4, 8, 16. Each number is a result of multiplying the previous number with some value (in this example: 2/1=2).

Excel VBA Autofill xlGrowthTrend


10. VBA AutoFill in Excel Using xlLinearTrend

Dataset: Cells B3 and B4 have two values 1 and 2.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlLinearTrend").Range("B3:B4")
Set TargetRange = Worksheets("xlLinearTrend").Range("B3:B10")
SourceRange.autofill Destination: =TargetRange, Type:=xlLinearTrend
End Sub

Source Range: Cells B3:B4 in column B

Target Range: Cells B3:B10 in column B

Worksheet: xlLinearTrend

Type: xlLinearTrend

Result: Excel assumes the relationship as an additive so 1, 2 is extended as 3, 4, and 5. Each number is a result of the addition of the previous number with some value (in this example: 2-1=1).


11. Convert Text to Columns Using xlFlashFill 

Dataset: Here we have the list of ICC World Cup Winners with the year of winning.

Code:

Sub autofill()
Set SourceRange = Worksheets("xlLinearTrend").Range("B3:B4")
Set fillRange = Worksheets("xlLinearTrend").Range("B3:B10")
SourceRange.autofill Destination: =fillRange, Type:=xlLinearTrend
End Sub

Source Range: Cells C3:B5 in column B

Target Range: Cells C3:C14 in column B

Worksheet: xlFlashFill

Type: xlFlashFill

Result: We wanted to extract the numerical part i.e., winning years. So, we put the first 3 years manually to make Excel understand the pattern. After running the code rest of the cells get auto filled with winning years of the world cup winners.

Excel VBA Autofill xlFlashFill


Things to Remember

▶ While writing multiple codes it is better to keep the right sequence.

▶ It is important to write the source range and target range properly to get the desired result. Otherwise, once we run the code it will change the data unexpectedly.


Conclusion

Now, we know how to auto-fill a cell, a row, or a column using VBA in Excel. Hopefully, it would encourage you to use this functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo