Have a Formula Continue to the Last Filled Cell
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.
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!
Further Readings
- How to Number Columns in Excel Automatically (5 Easy Ways)
- Use Autofill Formula in Excel (6 Ways)
- How to Apply AutoFill Shortcut in Excel (7 Methods)
- Fix: Excel Autofill Not Working (7 Issues)
Source: https://www.exceldemy.com/excel-vba-autofill-formula-to-last-row/
0 Response to "Have a Formula Continue to the Last Filled Cell"
Post a Comment