Autofill to Last Row Using VBA in Excel

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:

NameValueDescription
xlFillDefault0Excel determines the formats and values used to populate the target range.
xlFillCopy1Copy the formats and values from the source to the destination range, repeating if needed.
xlFillSeries2Extend 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.
xlFillFormats3Copy only the formats from the source to the target range, repeating if needed.
xlFillValues4Copy only the values from the source to the destination range, repeating if needed.
xlFillDays5Extend 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.
xlFillWeekdays6Extend 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.
xlFillMonths7Extend the months’ names in the source range into the target range. Copy formats from the source to the destination range, repeating if needed.
xlFillYears8Extend the years in the starting range into the target range. Copy formats from the source to the destination range, repeating if needed.
xlLinearTrend9Extend 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.
xlGrowthTrend10Extend 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.
xlFlashFill11Extend 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.

Data set to autofill using vba

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 Sub

When you run the subroutine, it auto-fills column B of the dataset with the same value 20:

number Autofill done by Vba

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:

Data set to autofill sequential numbers to the last used row

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 Sub

When 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:

Sequential numbers fell to the last filled row autofill vba

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:

Names data set in Excel

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 Sub

When you execute the code, it fills column B of the dataset with numbers that have a multiplicative relationship:

dataset autofill vba with numbers 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:

Names data set to autofill days

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 Sub

When 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:

Names auto fill till the last row using vba

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:

Student names data set with data graduation

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 Sub

Notice 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:

Dates auto filled with vba till the last row

Example #6: VBA to AutoFill Formula to Last Used Row 

Suppose you have the dataset below on Sheet6 of the current workbook:

Data set with item price and quantity

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 Sub

When you run the code, it enters the multiplication formula in cell D2 and autofills it to the last used row of the dataset:

VBA to auto fill formula to the last row

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:

Data set with item price quantity and total price

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 Sub

The 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:

Vb auto fill to fill the number format till the last row

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:

Data set with file names with extensions

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 Sub

When 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:

VAB to autofill flash fill to the last row

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.