What Does AutoFill Do in Excel VBA?
We have seen autofill in 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 provide the destination i.e. up to which cells the values needs to be filled.
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 either we copy & paste to the last cell or we just auto fill by double click 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 down till the required last cell.
In VBA too we can perform the task of AutoFill method. In this article, we will show you how we can use the autofill method and ways to write the code. Now we will see how we can use this tool in VBA coding.
How to Use AutoFill in VBA?
To use the autofill in VBA we need to understand the syntax of the auto fill method. Below is the syntax of the auto fill.
- Range (“A1”): What are the cells to identify the patter of the fill series.
- Destination: Till what cell 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’s see some simple to advanced examples of VBA AutoFill in excel.
Example #1 – xlFillDefault
First, enter 3 serial numbers in the first three cells.
In the VBA sub procedure mention the VBA range as Range (“A1: A3”)
Sub AutoFill_Example1() Range("A1:A3"). End Sub
Now access the AutoFill method.
Enter the destination as Range (“A1: A10”)
Select the Type as xlFillDefault.
Range("A1:A3").AutoFill Destination:=Range("A1:A10"), Type:=xlFillDefault
Now run the code we will get the serial numbers from 1 to 10.
Since we mentioned the end destination cell as A10 it has stopped there, we can enter the destination cell as the last cell of the excel.
Example #2 – xlFillCopy
For the same numbers, we will use the type as xlFillCopy.
Sub AutoFill_Example1() Range("A1:A3").AutoFill Destination:=Range("A1:A10"), Type:=xlFillCopy End Sub
I have the copy of the first three cells to the remaining cells.
Example #3 – xlFillMonths
For this example, I have entered the first three months in the first 3 cells.
Change the autofill type to xlFillMonths.
Sub AutoFill_Example1() Range("A1:A3").AutoFill Destination:=Range("A1:A10"), Type:=xlFillMonths End Sub
This will fill the month series.
Example #4 – xlFillFormats
For this example, I have entered numbers and applied formatting to those cells.
Now I will change the type to xlFillFormats.
Sub AutoFill_Example1() Range("A1:A3").AutoFill Destination:=Range("A1:A10"), Type:=xlFillFormats End Sub
Run this code and see what happens.
It has filled formats of the first three cells to the next three cells and again next three cells and so on.
Example #5 – xlFlashFill
For this example, I have entered a few values from cell A1 to A10 as shown in the below image.
From this list, I want to extract the numerical part. To tell excel about the pattern, in the first cell I will manually enter the numerical part of the first cell.
Now I will write the code as usual and change the type to xlFlashFill. This time we will use the B column range.
Sub AutoFill_Example1() Range("B1").AutoFill Destination:=Range("B1:B10"), Type:=xlFlashFill End Sub
If I run this code we will get the result like the below.
This is the overview of the VBA AutoFill method. Hope you have enjoyed it.
You can download this VBA AutoFill Excel Template from here – VBA AutoFill Excel Template
This has been a guide to VBA AutoFill in excel. Here we discuss How to Use Autofill in Excel VBA with various parameters like xlFillDefault, xlFillFormats, xlFlashFill, etc. You can learn more about VBA from following articles –