• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

VBA AutoFill

Home » Excel » Blog » VBA » VBA AutoFill

VBA AutoFill

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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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.

VBA Example 1

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.

AutoFill Module 1

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.

Popular Course in this category
Cyber Week Sale
VBA Training (3 Courses, 12+ Projects) 3 Online Courses | 13 Hands-on Projects | 45+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.7 (2,656 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (14 Courses, 21+ Projects)Excel Data Analysis Training (12 Courses, 6+ Projects)

Code:

Sub VBA_Autofill()

End Sub

VBA Example 1.1

Step 3: Select the range of the cells which we need to drag or extent for autofill. We have cell A1 and A2.

Code:

Sub VBA_Autofill()

Range ("A1:A2")

End Sub

Excel VBA Autofill Example 1.2

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

VBA Example 1.3

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

VBA Example 1.4

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

VBA Example 1.5

Step 7: Once done, compile the code and run it by clicking on the Play button which is located below the menu bar in VBA or Press F5. And we will see, in a moment list of months from Jan to Dec will get automatically filled from cell A3 to A12.

VBA 1.6

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.

VBA Eample 2

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

VBA Example 2.1

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

VBA Example 2.2

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

VBA Example 2.3

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

VBA Example 2.4

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

Code:

Sub VBA_Autofill2()

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

End Sub

VBA Example 2.5

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.

Excel VBA EXAMPLE 2

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.

VBA Example 3

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

Code:

Sub VBA_Autofill3()

End Sub

VBA SUB 3.1

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

VBA Range

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

Autofill Destination

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

Destination RANGE 2

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

RANGE 2

Step 6: At last, compile the code in one go, as it is a single line code and run. We will numbers from 1 to 4 are now copied to the cell till cell C12. And we can see all 4 numbers are copied twice.

Excel VBA AutoFILL 3

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.

EXAMPLE 4.1

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

Excel VBA Autofill Example 4.2

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

Excel Example 4.3

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

Code:

Sub VBA_Autofill4()

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

End Sub

Example 4.4

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

Example 4.5

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

Excel VBA Example 4.6

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.

example 4.7

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.

Recommended Articles

This is a guide to VBA Autofill. Here we discuss how to use Excel VBA Autofill Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. Copy Paste Function in VBA
  2. Substring Excel Function
  3. VBA Subscript out of Range
  4. Excel ISNUMBER Formula

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar
Excel Functions Tutorials
  • VBA
    • VBA Max
    • VBA Environ
    • VBA Not
    • VBA LOOKUP
    • Programming in Excel
    • VBA Name Worksheet
    • VBA ScreenUpdating
    • VBA Do Loop
    • VBA Declare Array
    • VBA Macros
    • VBA Delete Sheet
    • VBA Editor
    • VBA Error Handling
    • VBA Goal Seek
    • VBA DateDiff
    • VBA On Error Resume Next
    • VBA Examples
    • VBA Counter
    • VBA Val
    • VBA Me
    • VBA Double
    • VBA Variable Types
    • VBA FreeFile
    • VBA Switch Case
    • VBA Unprotect Sheet
    • VBA Format Number
    • VBA ReDim
    • VBA IF Statements
    • VBA DoEvents
    • VBA Public Variable
    • VBA Columns
    • VBA Cdate
    • VBA Selection Range
    • VBA Hide Columns
    • VBA COUNTA
    • VBA Insert Row
    • VBA Protect Sheet
    • VBA AutoFill
    • VBA IF Not
    • VBA GetObject
    • VBA Debug Print
    • VBA Collection
    • VBA Text
    • VBA Randomize
    • VBA Variant
    • VBA Selection
    • VBA Right
    • VBA Date Format
    • VBA Rename Sheet
    • VBA Paste Values
    • VBA InputBox
    • VBA Conditional Formatting
    • VBA Pause
    • VBA Pivot Table
    • VBA Message Box
    • VBA OFFSET
    • VBA ByRef
    • FileCopy in VBA
    • VBA StrConv
    • VBA SUB
    • VBA Boolean
    • VBA Global Variables
    • VBA Worksheets
    • VBA IIF
    • VBA Close UserForm
    • VBA Variable Declaration
    • VBA Join
    • VBA Dictionary
    • VBA Operators
    • VBA Charts
    • VBA For Each Loop
    • VBA FileDialog
    • VBA Format
    • VBA MID
    • VBA GetOpenFileName
    • VBA DatePart
    • VBA Code
    • VBA Option Explicit
    • VBA FileSystemObject (FSO)
    • VBA ASC
    • VBA UserForm
    • VBA Cells
    • VBA InStrRev
    • VBA Class Module
    • VBA Constants
    • VBA Length of String
    • VBA DateSerial
    • VBA StrComp
    • VBA Array Length
    • VBA Check File Exists
    • VBA Ubound
    • VBA CDBL
    • VBA Activate Sheet
    • VBA DateValue
    • VBA Borders
    • VBA Comment
    • VBA Intersect
    • VBA Workbook
    • VBA Concatenate
    • VBA Name
    • VBA Find and Replace
    • VBA Tutorial
    • VBA CSTR
    • VBA Save As
    • VBA Hyperlink
    • VBA Print
    • VBA Switch
    • VBA END
    • VBA Like
    • VBA Set
    • VBA excel programming
    • VBA Call Sub
    • VBA Object
    • VBA RoundUp
    • VBA ArrayList
    • VBA Named Range
    • VBA PowerPoint
    • VBA Block Comment
    • VBA OverFlow Error
    • VBA Insert Column
    • VBA Lcase
    • VBA List Box
    • VBA Delete File
    • VBA Clear Contents
    • VBA TextBox
    • VBA Font Color
    • VBA Range Cells
    • VBA INT
    • VBA UCASE
    • VBA Value
    • VBA Remove Duplicates
    • VBA Break for Loop
    • VBA Sleep
    • VBA Do Until Loop
    • VBA Union
    • VBA Long
    • VBA Copy Paste
    • VBA Data Types
    • VBA Delete Column
    • VBA Enum
    • VBA IsEmpty
    • VBA 1004 Error
    • VBA RegEx
    • VBA IsNumeric
    • VBA Paste
    • VBA Transpose
    • VBA Left
    • VBA Delete Row
    • VBA Integer
    • VBA Active Cell
    • VBA InStr
    • VBA Round
    • VBA Subscript out of Range
    • VBA Dim
    • VBA Replace
    • VBA Sort
    • VBA String
    • VBA Split
    • VBA Wait
    • VBA MOD
    • VBA Time
    • VBA TIMER
    • VBA While Loop
    • VBA Date
    • Excel VBA MsgBox
    • VBA IFError
    • VBA Color Index
    • VBA Match
    • VBA Case
    • VBA Arrays
    • VBA GoTo
    • VBA On Error
    • VBA Range
    • VBA Do While Loop
    • VBA Number Format
    • VBA Loops
    • VBA TRIM
    • VBA Find
    • VBA Select Case
    • VBA Else If
  • Excel Functions (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Lookup Reference Functions in Excel (30+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Statistical Functions in Excel (55+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • VBA Training
  • Excel Advanced Training
  • Excel Data Analysis Training
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

© 2019 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download VBA AutoFill Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Cyber Week Offer - Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More