WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA AutoFill

VBA AutoFill

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 us 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, we can perform the task of the 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.

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 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.

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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

vba autof Example 1.6

Since we mentioned the end destination cell as A10, it has stopped there, and 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.

Code:

Sub AutoFill_Example1()

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

End Sub

vba autof Example 2.1

I have a copy of the first three cells to the remaining cells.

vba autof Example 2.2

Example #3 – xlFillMonths

For this example, I have entered the first three months in the first 3 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

 

This will fill the month series.

vba autof Example 3.3

Example #4 – xlFillFormats

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

vba autof Example 4.1

 

Now I 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 of the first three cells to the next three cells and again, the 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.

vba autof Example 5.1

 

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.

vba autof Example 5.2

 

Now I 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 I run this code, we will get the result like the below.

vba autof example 5.4

 

This is the overview of the VBA AutoFill method. I hope you have enjoyed it.

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

Recommended Articles

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 the following articles –

  • Switch Case Statement in VBA
  • VBA AND Function
  • Count in VBA
  • Copy Paste in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More