VBA AutoFill

Publication Date :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

arrow

What Does AutoFill Do in Excel VBA?

We have seen AutoFill in the worksheet, where the cells are automatically filled based on the values in the previous cells above it. We can use VBA so that Excel does the task for us. To do this, we use the Selection.AutoFill method, and we must provide the destination, i.e., up to which cells it must fill the values.

The best use of VBA AutoFill comes when we need to fill the formula of the first cell to the cell of the column. We usually apply the formula in the first cell. Then, we copy and paste to the last cell or just auto-fill by double-clicking on the little arrow key. Another best example of using autofill in excel is when we need to insert serial numbers. We usually type the first three numbers; then, we drag them down to the required last cell.

In VBA, we can perform the task of the AutoFill method. This article will show you how to use the autofill method and write the code. Now, we will see how we can use this tool in VBA coding.

Table of contents
VBA AutoFill

How to Use AutoFill in VBA?

To use the AutoFill in VBA, we need to understand the syntax of the AutoFill method. Below is the syntax of the AutoFill.

vba autofill formula
  • Range (“A1”): What are the cells to identify the pattern of the fill series.
  • Destination: Till what cell do you want to continue the fill series pattern. Here, we need to mention the full range of cells.
  • Type as xlAutoFillType: Here, we can select the series fill type. Below are the list of items in this parameter – xlFillCopy, xlFillDays, xlFillDefault, xlFillFormats, xlFillMonths, xlFillSeries, xlFillValues, xlFillWeekdays, xlFillYears, xlFlashFill, xlGrowthTrend, xlLinearTrend.

Examples of AutoFill in Excel VBA

Let us see some simple to advanced examples of VBA AutoFill in excel.

Example #1 - xlFillDefault

First, enter 3 serial numbers in the first three cells.

vba autof Example 1.1

In the VBA subprocedure, mention the VBA range as Range (“A1: A3”)

Code:

Sub AutoFill_Example1()

Range("A1:A3").

End Sub
vba autof Example 1.2

Now, access the AutoFill method.

vba autof Example 1.3

Enter the destination as Range (A1: A10)

Code:

Range("A1:A3").AutoFill Destination:=Range("A1:A10")
vba autof Example 1.4

Select the “Type” as xlFillDefault.

Code:

Range("A1:A3").AutoFill Destination:=Range("A1:A10"), Type:=xlFillDefault
vba autof Example 1.5

Now, run the code. We will get the serial numbers from 1 to 10.

vba autof Example 1.6

Since we mentioned the end destination cell as A10, it has stopped there. Therefore, we can enter the destination cell as the last cell of the Excel.

Example #2 - xlFillCopy

We will use the “Type” for the same numbers as xlFillCopy.

Code:

Sub AutoFill_Example1()

Range("A1:A3").AutoFill Destination:=Range("A1:A10"), Type:=xlFillCopy

End Sub
vba autof Example 2.1

We have a copy of the first three cells for the remaining cells.

vba autof Example 2.2

Example #3 - xlFillMonths

We have entered the first three months in the first three cells.

vba autof Example 3.1

Change the AutoFill "Type" to xlFillMonths.

Code:

Sub AutoFill_Example1()

Range("A1:A3").AutoFill Destination:=Range("A1:A10"), Type:=xlFillMonths

End Sub
vba autof Example 3.2

It will fill the month series.

vba autof Example 3.3

Example #4 - xlFillFormats

For this example, we have entered numbers and applied formatting to those cells.

vba autof Example 4.1

Now, we will change the "Type" to xlFillFormats.

Code:

Sub AutoFill_Example1()

Range("A1:A3").AutoFill Destination:=Range("A1:A10"), Type:=xlFillFormats

End Sub
vba autof Example 4.2

Run this code and see what happens.

vba autof Example 4.3

It has filled formats from the first three cells to the next three cells and, again, the next three cells.

Example #5 - xlFlashFill

For this example, we have entered a few values from cell A1 to A10, as shown in the below image.

vba autof Example 5.1

From this list, we want to extract the numerical part. To tell Excel about the pattern, in the first cell, we will manually enter the numerical part of the first cell.

vba autof Example 5.2

We will write the code as usual and change the "Type" to xlFlashFill. This time we will use the B column range.

Code:

Sub AutoFill_Example1()

Range("B1").AutoFill Destination:=Range("B1:B10"), Type:=xlFlashFill

End Sub
vba autof Example 5.3

If we run this code, we will get the result like the below.

vba autof example 5.4

It is an overview of the VBA AutoFill method. We hope you have enjoyed it.

You can download this VBA AutoFill Excel Template from here - VBA AutoFill Excel Template