Trending October 2023 # How To Use Vba Autofill Function In Excel? # Suggested November 2023 # Top 12 Popular

You are reading the article How To Use Vba Autofill Function In Excel? updated in October 2023 on the website Nhunghuounewzealand.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Use Vba Autofill Function In Excel?

VBA AutoFill in Excel

Sometimes in excel when we work on similar kind of data set which keeps on repeating, excel itself suggests sequential values as AutoFill. This is quite easy to implement and use. As mostly we get a sequence of autofill in the form of Numbers, Weekdays, Months or any number series. If we create autofill or not but excel sometimes create the Autofill to accompany in completing the task quickly.

How to Use VBA AutoFill Function in Excel?

We will learn how to use a VBA Autofill function with few examples in excel.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA AutoFill Excel Template here – VBA AutoFill Excel Template

Example #1 – VBA AutoFill

Let’s see an example where we will autofill months names in a proper sequence. In column A we have name of 2 months Jan and Fed in short. And we will write a code which will fill rest of the months automatically till Dec.

Follow the below steps to use VBA AutoFill function in Excel:

Step 1: For this, we need a module. Go to Insert menu and select the module as shown below.

Step 2: After we get a module, in that write subcategory of VBA Autofill or in any other name of your choice as shown below.

Code:

Sub

VBA_Autofill()

End Sub

Code:

Sub

VBA_Autofill() Range ("A1:A2")

End Sub

Step 4: Now select the function we need to perform which is “Autofill”, allowing the Destination to get selected as shown below.

Code:

Sub

VBA_Autofill() Range("A1:A2").AutoFill Destination:=

End Sub

Step 5: Now select the range where we will need to autofill the names of months till Dec. We can select any range but selecting the same range makes it easy to implement.

Code:

Sub

VBA_Autofill() Range("A1:A2").AutoFill Destination:=Range("A1:A12")

End Sub

Step 6: Now here comes the moment, when we will select the time of autofill we need. Select the Type as xlFillMonths as shown below.

Code:

Sub

VBA_Autofill() Range("A1:A2").AutoFill Destination:=Range("A1:A12"), Type:=xlFillMonths

End Sub

Example #2 – VBA AutoFill

In this example we will see, how to Autofill numbers. We have column B with some numbers from 1 to 4 as shown below. Now we will Autofill the numbers till 10 automatically.

Step 1: For this we need a module where will implement the code for AutoFill the numbers. Open a module and create a sub category in the name of VBA AutoFill as shown below.

Code:

Sub

VBA_Autofill2()

End Sub

Step 2: Now select the range of the cells which are currently available which is from B1 to B4.

Code:

Sub

VBA_Autofill2() Range("B1:B4").

End Sub

Step 3: Select the AutoFill function followed by the Destination command as shown below.

Code:

Sub

VBA_Autofill2() Range("B1:B4").AutoFill Destination:

End Sub

Step 4: Now select the range of the cells till we want to autofill the numbers. Here we are making it till number 10. So, select the range from cell B1 to B10.

Code:

Sub

VBA_Autofill2() Range("B1:B4").AutoFill Destination:=Range("B1:B10"),

End Sub

Step 5: Now for Auto filling numbers, we will select Type as xlFillDefault is meant only for number kind of things.

Sub

VBA_Autofill2() Range("B1:B4").AutoFill Destination:=Range("B1:B10"), Type:=xlFillDefault

End Sub

Step 6: Now compile the code and run to get the output. We will see numbers are auto filled from 1 to 10 as shown below.

Example #3 – VBA AutoFill

There is another way to autofill the content of any columns. This can be done for anything. Numbers, Text or any combination. We have considered the numbers here in Column C from 1 to 4 as shown below.

Step 1: Open a module, in that create the subcategory of VBA Autofill shown below.

Code:

Sub

VBA_Autofill3()

End Sub

Step 2: As the same above examples, we will select the range of the cells which has the data and need to get Autofill.

Code:

Sub

VBA_Autofill3() Range("C1:C4").

End Sub

Step 3: In the same manner as shown in the above examples, select the Autofill function followed by Destination command.

Code:

Sub

VBA_Autofill3() Range("C1:C4").AutoFill Destination:=

End Sub

Step 4: Select the range of cells till where we need to autofill the cell contents. Here we are selecting the range of cell C1 to C12.

Code:

Sub

VBA_Autofill3() Range("C1:C4").AutoFill Destination:=Range("C1:C12"),

End Sub

Step 5: Now select the Type of autofill we want. Here we are selecting xlFillCopy which will Copy and Paste the data available in selected range cells to destination cells.

Code:

Sub

VBA_Autofill3() Range("C1:C4").AutoFill Destination:=Range("C1:C12"), Type:=xlFillCopy

End Sub

Example #4 – VBA AutoFill

In this example we will see how to AutoFill format of cells to below destination cells. Format can be Bold, Italic or colored cells. Here we have columns D with some colored cells D1 to D3 as shown below.

Step 1: Open a new module and create a sub category of VBA AutoFill or in any other name. As we can see, here we have kept all the sub categories in numerical sequence.

Code:

Sub

VBA_Autofill4()

End Sub

Step 2: Select a range of those cells which has a colored background. Here those cells range is D1 to D3.

Code:

Sub

VBA_Autofill4() Range("D1:D3").

End Sub

Step 3: Now select AutoFill function with command Destination as shown below.

Code:

Sub

VBA_Autofill4() Range("D1:D3").AutoFill Destination:=

End Sub

Step 4: Now select the Destination range of cells where we need to autofill the format cells. Here we are selecting D1 to D10.

Code:

Sub

VBA_Autofill4() Range("D1:D3").AutoFill Destination:=Range("D1:D10"),

End Sub

Step 5: At last select the Type which we need to autofill in destination cells as xlFillFormat.

Code:

Sub

VBA_Autofill4() Range("D1:D3").AutoFill Destination:=Range("D1:D10"), Type:=xlFillFormat

End Sub

Step 6: Finally we can compile the code and run it. We will the color which are in cells D1 to D3 are auto filled to the cell till D10.

Pros of VBA AutoFill

It saves the time by automatically filling the content of the cells which need to be filled repeatedly.

Sequential data can be automatically filled to save file.

Things to Remember

Autofill can be used for formatting, copying, dragging for numbers and text.

If possible then keep the proper sequence of code while writing multiple codes.

It is always better to automate the data which we need to fill in same manner.

New version of MS Excel has function where if we enter one data set one time then next time we just need to fill 2-3 cells and it will automatically suggest to fill the entire range.