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.

**Table of Contents**hide

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

Now, drag this autofill handle down.

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

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:

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

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.

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

**Similar Readings**

**How to Repeat Rows a Specified Number of Times in Excel (4 Ways)****Number Rows Automatically in Excel (8 Methods)****How to Fill Column in Excel with Same Value (9 Tricks)****Apply AutoFill Shortcut in Excel (7 Methods)**

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

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:

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

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

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!