AutoFill Formula to Last Row with Excel VBA (5 Examples)

If you are working with Microsoft Excel for a while, you know the importance of AutoFill. In order to copy a formula across the row or the column, we use this method. Now, we can use this manually with the mouse. But, if you have a large row with data, it will be difficult to copy any formula to the last row or column. You can use the VBA code to AutoFill a formula to the last row or column with ease.

In this tutorial, you will learn to AutoFill a formula to the last row in Excel using VBA codes. This tutorial will be on point with suitable examples and proper illustrations. So, stay with us.

What Is AutoFill in Excel?

Now, autofill is a built-in feature of Microsoft Excel that allows users to automatically fill the rest of the rows or columns with values, formats, or formulas. You can identify it when you take the cursor to the right bottom corner of any cell. We call it the autofill handler of Excel.

Take a look at the following screenshot:

Here, you can see two numbers. We will fill the rest of the rows using the autofill in Excel.

First, select the range of cells B5:B6. After that, you will see the autofill handle in the right bottom corner.

Now, drag this autofill handle down.

As you can see, Excel automatically filled the rest of the cells of the column. This is the basic usage of the autofill in Excel.

How to Use AutoFill with Excel VBA

If you have a large number of rows or columns in a given range, you have to drag down the autofill handle to the last row or column. It is a very hectic process. Thankfully you can minimize it using the VBA codes in Excel. You can use the VBA code to autofill the cells with values, formulas, or formats.

The Generic Syntax:

Here,

4 Types of AutoFill in Excel VBA

In this section, I will discuss some of the types of autofill. We already saw that we can choose any autofill type using the xlAutoFillType.  Here, I am showing you some examples of that.

1. xlFillDefault

We already saw this type of autofill in the previous example.

Take a look at the screenshot:

Type the following VBA code to autofill the rest of the cells:

Sub xlDefault_type()

Range("B5:B6").AutoFill Destination:=Range("B5:B11"), Type:=xlFillDefault

End Sub

Now, run the VBA macro and after that, you will see the following:

It automatically fills the rest of the cells using VBA macro.

2. xlFillCopy

To copy the same values you can use the type xlFillCopy in the autofill type.

Take a look at the screenshot:

Type the following VBA code to autofill the rest of the cells:

Sub xlFillCopy_type()

Range("B5:B6").AutoFill Destination:=Range("B5:B11"), Type:=xlFillCopy

End Sub

Now, run the VBA macro and after that, you will see the following:

Excel fills the rest of the cells with the same pattern of values.

3. xlFillMonths

You can also fill months using the xlFillMonths autofill type.

Take a look at the screenshot:

Here, we have entered two months January and February.

Type the following VBA code to autofill the rest of the cells:

Sub xlFillMonths_type()

Range("B5:B6").AutoFill Destination:=Range("B5:B11"), Type:=xlFillMonths

End Sub

Now, run the VBA macro and after that, you will see the following:

Excel automatically understands the patterns and fills them with them.

4. xlFillFormats

You can also copy formats to other cells using the VBA autofill. For that, you have to use the xlFillFormats autofill type.

Take a look at the screenshot:

Here, we have filled the two cells with some colors.

Now, type the following VBA code to autofill the rest of the cells:

Sub xlFillFormats_type()

Range("B5:B6").AutoFill Destination:=Range("B5:B11"), Type:=xlFillFormats

End Sub

Now, run the VBA macro and after that, you will see the following:

As you can see, our VBA macro uses autofill to fill the rows of column B effectively.

AutoFill Formula to Last Row in Excel VBA: 5 Examples

1. VBA to AutoFill a Formula to Last Used Row

Similarly, you can autofill a formula using the VBA. if you have read the previous sections, you can easily perform this. The problem arises when you have to autofill the formula to the last row. You have to identify the last used row first. After that, the VBA code will automatically fill them.

Take a look at the following dataset:

Here, we have a sales dataset of some salespersons. Our goal is to add the sales of January and February to the Total column. After that, we will use the autofill method using the VBA to the last used row in Excel.

To execute this, type the following code:

Sub last_used_row()

Dim last_row As Long

last_row = Cells(Rows.Count, 2).End(xlUp).Row

Range("E5").Formula = "=SUM(C5:D5)"

Range("E5").AutoFill Destination:=Range("E5:E" & last_row)

End Sub

Now, run the VBA macro. After that, you will see the following output:

As you can see, our VBA code adds the first result and autofill the formula to the last row in Excel.

2. VBA AutoFill from ActiveCell to Last Row

Now, if you don’t want to use the particular range in the autofill method, you can use the active cell’s values, formulas, or formats. When you click on a cell and want to autofill the rest of the rows with the active cell’s formula, use this method.

It is similar to the previous example. We are using the previous dataset:

Now, we will add the sales of January and February and autofill the formula to the last row using Excel VBA.

Now, type the following code:

Sub autofill_active_cell()

Dim last_row As Long

last_row = Cells(Rows.Count, 2).End(xlUp).Row

ActiveCell.Formula = "=SUM(C5:D5)"

ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":E" & last_row)

End Sub

Now, select Cell E5.

After that, run the VBA macro to add the formula and autofill to the last row.

As you can see, we successfully used the VBA macro to autofill the formula to the last row.

3. AutoFill to Last Row Using Dynamic Range with Excel VBA

Previously we showed you some VBA macros to autofill to the last row in Excel. Now, in those examples, we already used dynamic ranges.

To understand static ranges, have a look at the following code:

Sub last_used_row()

Range("E5").Formula = "=SUM(C5:D5)"

Range("E5").AutoFill Destination:=Range("E5:E11”)

End Sub

We basically told Excel to enter a formula in Cell E5 and autofill it from Cell E5 to Cell E11. Here, we specified the range. What if you add more rows? In that case, our VBA won’t autofill those extra rows because we already told it to autofill a specific range.

To solve this problem I came up with a solution that can take a dynamic range:

Sub last_used_row()

Dim last_row As Long

last_row = Cells(Rows.Count, 2).End(xlUp).Row

Range("E5").Formula = "=SUM(C5:D5)"

Range("E5").AutoFill Destination:=Range("E5:E" & last_row)

End Sub

As you can see, we first find the last row by this line of code:

last_row = Cells(Rows.Count, 2).End(xlUp).Row

After that, we autofill to the last row using the dynamic range:

Range("E5").AutoFill Destination:=Range("E5:E" & last_row)

No matter how many rows you add to your dataset, this VBA code will be successful in identifying them. And it will autofill them with the formula.

Dynamic range basically means you don’t have to enter the range manually. Excel will update it from time to time.

Read More: How to Autofill Dynamic Range Using VBA in Excel

4. AutoFill to Last Column in Excel Using VBA

If you have read the previous sections to autofill to the last row, then you can autofill to the last column easily. You have to just find the last column number and autofill it with Excel VBA.

Take a look at the following dataset:

Here, we have a dataset that represents a 3-months budget of a person. Now, we will add the expenses of all months and autofill the formula to the last column using the Excel VBA.

Now, type the following code:

Sub last_used_column()

Dim last_row As Long

last_column = Cells(6, Columns.Count).End(xlToLeft).Column

Range("D9").Formula = "=SUM(D6:D8)"

Range("D9").AutoFill Destination:=Range("D9", Cells(9, last_column))

End Sub

After running the macro, you will see the following output:

As you can see, we successfully used the VBA to autofill the formula to the last column in Excel.

5. AutoFill Sequential Numbers to Last Row in Excel Using VBA

You can also autofill sequential numbers using the autofill in VBA. Here, you have to use xlFillSeries in the autofill type. Excel will understand the pattern and fill them with it.

Have a look at the following screenshot:

Here, we have some names. And we want to give them an ID in a sequential manner. To do this, we will use the VBA autofill.

Autofill Sequential Numbers with Static Range:

Sub sequential_number_2()

Range("C5").AutoFill Destination:=Range("C5:C11"), Type:=xlFillSeries

End Sub

After Running the code, you will see the output:

If you want to use the dynamic range, then use the following code:

Sub sequential_number_1()

Dim last_row As Long

last_row = Cells(Rows.Count, 2).End(xlUp).Row

Range("C5").AutoFill Destination:=Range("C5:C" & last_row), Type:=xlFillSeries

End Sub

In both cases, you will get the same output:

As you can see, we successfully used the VBA code to autofill sequential numbers in Excel.

💬 Things to Remember

✎ You can stop the autofill in Various ways if you don’t want it.

✎ You can not autofill multiple rows and columns simultaneously. Do it one by one.

Download Practice Workbook

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to autofill the formula to the last row using the VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Keep learning new methods and keep growing!

Further Reading

• Excel VBA: Autofill Method of Range Class Failed