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


Download Practice Workbook


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

What is AutoFill in Excel?

Now, drag this autofill handle down.

What is AutoFill in Excel?

As you can see, Excel automatically filled the rest of the cells. 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:

Range.AutoFill Destination, Type

How to Use AutoFill Using VBA

Here,

Range(“B5”): The cell that has the main pattern to fill the rest of the series.

Destination: Range of cells where you want to fill with the pattern series.

Type as xlAutoFillType: The series fill type. There are various autofill types that you can choose.

Read more: How to Use VBA AutoFill in Excel


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:

Examples of AutoFill Type in Excel VBA

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:

Examples of AutoFill Type in Excel VBA

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:

Examples of AutoFill Type in Excel VBA

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:

Examples of AutoFill Type in Excel VBA

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:

Examples of AutoFill Type in Excel VBA

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

Read more: How to AutoFill Months in Excel


5 Examples with AutoFill Formula to Last Row in Excel VBA

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

last_row = Cells(Rows.Count, 2).End(xlUp).Row: It returns the last used row from the column B. You can choose any column from your dataset.

Range(“E5”).Formula = “=SUM(C5:D5)”: We add the sales of Cell C5 and D5.

Range(“E5”).AutoFill Destination:=Range(“E5:E” & last_row): After getting the result, we use the autofill. It starts auto-filling from Cell E5 to the last used row that we got previously.

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

VBA to AutoFill a Formula to Last Used Row

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

Read more: How to Fill Down to Last Row with Data 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

last_row = Cells(Rows.Count, 2).End(xlUp).Row: It returns the last used row from the column B. You can choose any column from your dataset.

ActiveCell.Formula = “=SUM(C5:D5)”: We add the sales of Cell C5 and D5 in your selected cell.

ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & “:E” & last_row): After getting the result, we use the autofill. It starts auto-filling from the active cell to the last used row that we got previously.

Now, select Cell E5.

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

VBA AutoFill From Activecell to Last Row

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


Similar Readings


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 to identify 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: Excel VBA: Autofill Method of Range Class Failed


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

last_column = Cells(6, Columns.Count).End(xlToLeft).Column: It returns the last used column from the row 6. You can choose any row to start from your dataset.

Range(“D9”).Formula = “=SUM(D6:D8)”: We add the expenses of three months (Jan, Feb. Mar).

Range(“D9”).AutoFill Destination:=Range(“D9”, Cells(9, last_column)): After getting the result, we use the autofill. Here, our main row is Row number 9. All of our data will be in this row. It starts auto-filling from column D to the last used column that we got previously by the last_column.

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

AutoFill to Last Column in Excel Using VBA

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

Read more: How to Autofill a 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:

AutoFill Sequential Numbers to Last Row in Excel Using VBA

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:

AutoFill Sequential Numbers to Last Row in Excel Using VBA

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

last_row = Cells(Rows.Count, 2).End(xlUp).Row: It returns the last used row from the column B.

Range(“C5”).AutoFill Destination:=Range(“C5:C” & last_row), Type:=xlFillSeries: It will take the ID of Cell C5 and autofill to the last used row in Column C using the xlFillSeries,

In both cases, you will get the same output:

AutoFill Sequential Numbers to Last Row in Excel Using VBA

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

Read more: Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows


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


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Further Readings

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo