The ‘Range.AutoFill’ method in Excel VBA allows you to automatically fill a cell range with values, formats, or formulas.
In this tutorial, I will show you several examples of auto-filling to the last used row using VBA’s ‘Range.AutoFill’ method.
The ‘Range.AutoFill’ Method
The ‘Range.AutoFill’ method performs an autofill on the cells in the specified cell range.
The syntax of the method:
expression.AutoFill (Destination As Range, [Type As XlAutoFillType = xlFillDefault])
expression is a variable representing a Range object.
The ‘Destination’ is a required argument of the ‘Range’ data type. This argument specifies the cells to be filled and must include the source range.
The ‘Type’ parameter is optional and specifies the fill type. It determines how the target range is filled based on the source range’s contents.
The table below describes the constants you can use to specify the fill type:
| Name | Value | Description |
|---|---|---|
| xlFillDefault | 0 | Excel determines the formats and values used to populate the target range. |
| xlFillCopy | 1 | Copy the formats and values from the source to the destination range, repeating if needed. |
| xlFillSeries | 2 | Extend the data in the source range into the destination range as a sequence (for example, ‘1, 2’ is extended as ‘3, 4, 5, 6’). Copy formats from the source to the target range, repeating if needed. |
| xlFillFormats | 3 | Copy only the formats from the source to the target range, repeating if needed. |
| xlFillValues | 4 | Copy only the values from the source to the destination range, repeating if needed. |
| xlFillDays | 5 | Extend the names of the days of the week in the starting range into the destination range. Copy formats from the source to the destination range, repeating if needed. |
| xlFillWeekdays | 6 | Extend the days of the workweek’s names in the source range into the target range. Copy formats from the source to the destination range, repeating if needed. |
| xlFillMonths | 7 | Extend the months’ names in the source range into the target range. Copy formats from the source to the destination range, repeating if needed. |
| xlFillYears | 8 | Extend the years in the starting range into the target range. Copy formats from the source to the destination range, repeating if needed. |
| xlLinearTrend | 9 | Extend the numbers from the starting range into the destination range, assuming that the relationships between the numbers are additive (for example, ‘1, 2,’ is extended as ‘3, 4, 5’). Copy formats from the starting to the destination range, repeating if needed. |
| xlGrowthTrend | 10 | Extend the numbers in the starting range into the destination range, assuming their relationships are multiplicative (e.g., ‘1, 2’ extends to ‘4, 8, 16, 32’). Copy formats from the source to the destination range, repeating if needed. |
| xlFlashFill | 11 | Extend the values from the starting range into the destination range based on the detected pattern of previous user actions, repeating if needed. |
Example #1: VBA to AutoFill The Same Value to the Last Used Row
Assume you have the dataset below on Sheet1 of the current workbook.
You can use the subroutine below to enter the value 20 in cell B2 and autofill it to the last used row of the dataset:
Sub AutoFillSameValues()
Dim LastRow As Long
Dim SourceRange As Range
Dim FillRange As Range
LastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Range("B2") = 20
Set SourceRange = Worksheets("Sheet1").Range("B2")
Set FillRange = Worksheets("Sheet1").Range("B2:B" & LastRow)
SourceRange.AutoFill Destination:=FillRange
End SubWhen you run the subroutine, it auto-fills column B of the dataset with the same value 20:
Explanation of the Code
The code utilizes column A’s ‘Range.End (xlUp).Row’ property to determine the last used row of the dataset.
The code enters the value 20 in cell B2 and populates the subsequent cells in the column with the same value down to the last used row of the dataset.
Also read: Count Rows using VBA in Excel
Example #2: VBA to AutoFill Sequential Numbers to the Last Used Row
Suppose you have the dataset below on Sheet2 of the current workbook:
You can use the VBA code below to number the list from cell A2 to the last used row of the dataset:
Sub AutoFillSequentialNumbers()
Dim LastRow As Long
Dim SourceRange As Range
Dim FillRange As Range
LastRow = Worksheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet2").Range("A2") = 1
Set SourceRange = Worksheets("Sheet2").Range("A2")
Set FillRange = Worksheets("Sheet2").Range("A2:A" & LastRow)
SourceRange.AutoFill Destination:=FillRange, Type:=xlFillSeries
End SubWhen you execute the code, it auto-fills column A of the dataset with numbers in the sequence of 1, 2, 3, 4, and so on:
Explanation of the Code
This code uses the ‘Range.End (xlUp).Row’ property of column B to determine the last used row in the dataset.
It then enters the value 1 in cell A2 and fills in the rest of the column with sequential numbers going down to the last used row of the data range.
Also read: VBA to Delete Rows Based On Cell Value
Example #3: VBA to AutoFill Numbers With Multiplicative Relationship to Last Used Row
Suppose you have the dataset below on Sheet3 of the current workbook:
You can use the VBA code below to autofill column B with numbers that have a multiplicative relationship starting at cell B2 to the last used row of the dataset:
Sub AutoFillMultiplicativeNumbers()
Dim LastRow As Long
Dim SourceRange As Range
Dim FillRange As Range
LastRow = Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet3").Range("B2") = 1
Worksheets("Sheet3").Range("B3") = 2
Set SourceRange = Worksheets("Sheet3").Range("B2:B3")
Set FillRange = Worksheets("Sheet3").Range("B2:B" & LastRow)
SourceRange.AutoFill Destination:=FillRange, Type:=xlGrowthTrend
End SubWhen you execute the code, it fills column B of the dataset with numbers that have a multiplicative relationship:
Explanation of the Code
This code uses the ‘Range.End (xlUp).Row’ property of column A to determine the last used row in the dataset.
It then enters the value 1 in cell B2 and 2 in cell B3 and fills the rest of the column with numbers with a multiplicative relationship going down to the last used row of the data range.
Also read: VBA to Hide Rows Based On Cell Values
Example #4: VBA to AutoFill Names of Days to Last Used Row
Suppose you have the dataset below on Sheet4 of the current workbook:
You can use the VBA code below to autofill column B with names of the days of the week starting at cell B2 to the last used row of the dataset:
Sub AutoFillDays()
Dim LastRow As Long
Dim SourceRange As Range
Dim FillRange As Range
LastRow = Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet4").Range("B2") = "Monday"
Set SourceRange = Worksheets("Sheet4").Range("B2")
Set FillRange = Worksheets("Sheet4").Range("B2:B" & LastRow)
SourceRange.AutoFill Destination:=FillRange, Type:=xlFillDays
End SubWhen you execute the code, it populates column B of the dataset with the names of the days of the week starting with ‘Monday’ in cell B2:
Explanation of the Code
This code uses the ‘Range.End (xlUp).Row’ property of column A to determine the last used row in the dataset.
It then enters ‘Monday’ in cell B2 and fills in the rest of the column with the other days of the week going down to the last used row of the data range.
Note: To fill in only the names of weekdays, change the ‘Type’ parameter in the code to ‘xlFillWeekdays.’
If you want to AutoFill months, modify the code to enter ‘January’ in cell B2 and change the ‘Type’ parameter to ‘xlFillMonths.’
Also read: VBA to Find Last Row in Excel
Example #5: VBA to AutoFill Dates to Last Used Row
Suppose you have the dataset below on Sheet5 of the current workbook:
You can use the VBA code below to autofill dates that fall in sequential years in column B of the dataset:
Sub AutoFillYears()
Dim LastRow As Long
Dim SourceRange As Range
Dim FillRange As Range
LastRow = Worksheets("Sheet5").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet5").Range("B2") = DateSerial(2020, 8, 25)
Set SourceRange = Worksheets("Sheet5").Range("B2")
Set FillRange = Worksheets("Sheet5").Range("B2:B" & LastRow)
SourceRange.AutoFill Destination:=FillRange, Type:=xlFillYears
End SubNotice that the code uses the ‘DateSerial’ function to specify the start date’s year, month, and day components.
When you run the code, it auto-fills dates that fall in sequential years in column B, starting from cell B2 to the last used row of the dataset:
Also read: VBA to Insert Row in Excel
Example #6: VBA to AutoFill Formula to Last Used Row
Suppose you have the dataset below on Sheet6 of the current workbook:
You can use the following VBA code to multiply the values in columns B and C and autofill the formula to the last used row of the dataset:
Sub AutoFillFormula()
Dim LastRow As Long
Dim SourceRange As Range
Dim FillRange As Range
LastRow = Worksheets("Sheet6").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet6").Range("D2").Formula = "=B2*C2"
Set SourceRange = Worksheets("Sheet6").Range("D2")
Set FillRange = Worksheets("Sheet6").Range("D2:D" & LastRow)
SourceRange.AutoFill Destination:=FillRange
End SubWhen you run the code, it enters the multiplication formula in cell D2 and autofills it to the last used row of the dataset:
Explanation of the Code
The code utilizes column A’s ‘Range.End (xlUp)’ property to determine the last used row of the dataset. The code automatically populates column D in Sheet1 with the product of corresponding values from columns B and C, extending the formula down to the last row with data.
Example #7: VBA to AutoFill a Number Format to Last Used Row
Suppose you have the dataset below on Sheet7 of the current workbook:
Notice that the values in column D do not have a specific number format.
You can use the VBA code below to autofill an accounting number format in column D, starting from cell D2 to the last used row of the dataset:
Sub AutoFillNumberFormat()
Dim LastRow As Long
Dim SourceRange As Range
Dim FillRange As Range
LastRow = Worksheets("Sheet7").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet7").Range("D2").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Set SourceRange = Worksheets("Sheet7").Range("D2")
Set FillRange = Worksheets("Sheet7").Range("D2:D" & LastRow)
SourceRange.AutoFill Destination:=FillRange, Type:=xlFillFormats
End SubThe code utilizes column A’s ‘Range.End (xlUp)’ property to determine the last used row of the dataset. The code applies the accounting number format to the value in cell D2. The code extends the number format down to the last row with data as seen below:
Example #8: VBA to FlashFill
You can use Excel VBA to extend the values from the source range into the destination range based on the pattern shown by previous user actions.
Suppose you have the dataset below on Sheet8 of the current workbook:
You can use the VBA code below to autofill the values in column A, excluding the extensions in column B, starting from cell B2 to the last used row of the dataset:
Sub FlashFill()
Dim LastRow As Long
Dim SourceRange As Range
Dim FillRange As Range
LastRow = Worksheets("Sheet8").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet8").Range("B2") = "report1"
Set SourceRange = Worksheets("Sheet8").Range("B2")
Set FillRange = Worksheets("Sheet8").Range("B2:B" & LastRow)
SourceRange.AutoFill Destination:=FillRange, Type:=xlFlashFill
End SubWhen you execute the code, it places the text ‘report1’ in cell B2. AutoFill detects the pattern and fills the values in column A, excluding the extensions in column B of the dataset:
In this tutorial, I showed you several examples of auto-filling to the last used row in VBA. I hope you found the article helpful.
Other articles you may also like:
