Al Arafat Siddique
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.
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 SubSource 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.
Read more: How to Auto Number or Renumber after Filter in Excel
 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.
Code:
Sub autofill()
Set SourceRange = Worksheets("xlFillDays").Range("B3:B4")
Set TargetRange = Worksheets("xlFillDays").Range("B3:B11")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillDays
End SubSource 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.
Read more: How to Autofill Days of Week Based on Date in Excel
3. Autofill Cells with the Default AutoFill Type of Excel VBA- xlFillDefault
Dataset: In column B cells B3:B4 contain numbers 1 and 3.
Code:
Sub autofill()
Set SourceRange = Worksheets("xlFillDefault").Range("B3:B5")
Set TargetRange = Worksheets("xlFillDefault").Range("B3:B11")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillDefault
End SubSource 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
Read more: How to Repeat Number Pattern in Excel
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.
Code:
Sub autofill ()
Set SourceRange = Worksheets("xlFillFormats").Range("B3:B5")
Set fillRange = Worksheets("xlFillFormats").Range("B3:B11")
SourceRange.autofill Destination:=fillRange, Type:=xlFillFormats
End SubSource 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.
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.
Code:
Sub autofill ()
Set SourceRange = Worksheets("xlFillMonths").Range("B3:B5")
Set TargetRange = Worksheets("xlFillMonths").Range("B3:B14")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillMonths
End SubSource 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.
Read more: How to AutoFill Months in Excel
6. Get a Series Using xlFillSeries AutoFill Type
Dataset: The cells B3:B5 of column B contain three numbers 1, 4, and 7.
Code:
Sub autofill()
Set SourceRange = Worksheets("xlFillSeries").Range("B3:B5")
Set TargetRange = Worksheets("xlFillSeries").Range("B3:B10")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillSeries
End SubSource 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.
Read more: Applications of Excel Fill Series
Similar Readings
- How to Apply AutoFill Shortcut in Excel (7 Methods)
- Use Autofill Formula in Excel (6 Ways)
- Fix: Excel Autofill Not Working (7 Issues)
- How to Use Flash Fill in Excel to Split Data (4 Quick Ways)
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 SubSource 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.
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.
Code:
Sub autofill()
Set SourceRange = Worksheets("xlFillYears").Range("B3:B4")
Set TargetRange = Worksheets("xlFillYears").Range("B3:B10")
SourceRange.autofill Destination:= TargetRange, Type:=xlFillYears
End SubSource 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.
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 SubSource 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).
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 SubSource 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 SubSource 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.
Read more: How to Autofill a Column in Excel
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.
