Automating Formula AutoFill in Excel with VBA: A Comprehensive Guide (5 Practical Examples)

If you've been navigating Microsoft Excel for some time, you're likely familiar with the indispensable function of AutoFill. While manually using the mouse to copy a formula across rows or columns is feasible for smaller datasets, the process becomes cumbersome with larger sets of data. Enter Visual Basic for Applications (VBA) – a powerful tool that allows you to seamlessly AutoFill a formula to the last row or column with precision and efficiency.

In this tutorial, we will delve into the world of VBA codes, providing you with clear examples and illustrations on how to automate the AutoFill process in Excel. Let's explore the power of VBA to enhance your workflow.

Before we dive into the VBA magic, let's briefly revisit what AutoFill is in Excel. AutoFill is an in-built feature in Microsoft Excel designed to automatically populate the remaining rows or columns with values, formats, or formulas. You can easily spot it by moving your cursor to the lower-right corner of any cell – this nifty feature is known as the AutoFill handler.

Now, let's proceed with the VBA journey to effortlessly AutoFill formulas to the last row in Excel. Stay tuned for insightful examples and step-by-step guidance.

Understanding AutoFill in Excel:

AutoFill is an inherent capability within Microsoft Excel, empowering users to effortlessly populate the remaining rows or columns with values, formats, or formulas. Recognition of this feature is simple – just position your cursor in the lower-right corner of any cell, commonly referred to as the AutoFill handler.

Refer to the accompanying screenshot:

In the provided example, observe two numbers in cells B5 and B6. Our goal is to extend this sequence to fill the subsequent rows using the AutoFill feature in Excel.

Commence by selecting the range of cells from B5 to B6. Subsequently, locate the AutoFill handle positioned in the lower-right corner.

Upon identifying the AutoFill handle, drag it downward.

Witness Excel's automatic population of the remaining cells in the column. This exemplifies the fundamental application of the AutoFill feature in Excel.

Utilizing AutoFill with Excel VBA:

When dealing with an extensive number of rows or columns within a specific range, manually dragging down the AutoFill handle to reach the last row or column can be a cumbersome task. Fortunately, this can be streamlined using VBA codes in Excel. VBA allows you to efficiently AutoFill cells with values, formulas, or formats, simplifying the process.

The Generalized Syntax:

Range.AutoFill Destination, Type

Here's the breakdown:

- Range(“B5”): Refers to the cell containing the primary pattern to be extended throughout the series.

- Destination: Denotes the range of cells where the pattern series is to be filled.

- Type as xlAutoFillType: Specifies the type of series fill. Various AutoFill types are available for selection, providing flexibility in the AutoFill process.

 

Exploring 4 AutoFill Types in Excel VBA:

In this segment, we will delve into various types of AutoFill in Excel VBA. As mentioned earlier, the xlAutoFillType parameter allows us to select different AutoFill types. Let's explore some examples:

1. xlFillDefault:

In our previous example, we encountered this AutoFill type.

Observe the provided screenshot:

Utilize the following VBA code to AutoFill the remaining cells:

Sub xlDefault_type()

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

End Sub

Execute the VBA macro, and the outcome will be as follows:

Witness the automatic population of the remaining cells facilitated by the VBA macro.

2. xlFillCopy:

For duplicating identical values, the xlFillCopy type in the AutoFill category can be employed.

Refer to the provided screenshot:

Insert the following VBA code to automatically fill the remaining cells:

Sub xlFillCopy_type()

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

End Sub

Execute the VBA macro, and the result will be as follows:

Excel seamlessly populates the remaining cells with the identical pattern of values.

3. xlFillMonths:

To populate cells with months, leverage the xlFillMonths AutoFill type.

Refer to the accompanying screenshot:

In this example, we've inputted two months, January and February.

Insert the following VBA code to AutoFill the remaining cells:

Sub xlFillMonths_type()

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

End Sub

Execute the VBA macro, and the outcome will be as follows:

Excel intelligently discerns the month patterns and seamlessly fills the cells accordingly.

4. xlFillFormats:

To replicate formats across cells using VBA AutoFill, employ the xlFillFormats AutoFill type.

Examine the provided screenshot:

In this instance, two cells have been adorned with distinct colors.

Now, input the following VBA code to AutoFill the remaining cells:

Sub xlFillFormats_type()

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

End Sub

Execute the VBA macro, and the result will be as follows:

As evident, our VBA macro adeptly utilizes AutoFill to efficiently propagate the formatting across the rows of column B.

Automating Formula AutoFill in Excel with VBA: A Comprehensive Guide (5 Practical Examples)

1. VBA for AutoFilling a Formula to the Last Used Row:

Similarly, leveraging VBA enables you to effortlessly AutoFill a formula, building upon the concepts discussed in preceding sections. The challenge arises when you need to extend the formula to the last used row. This necessitates identifying the last used row before implementing the VBA code to execute the AutoFill.

Consider the provided dataset:

In this scenario, we have a sales dataset featuring salespersons. The objective is to sum the sales of January and February, adding the results to the Total column. Subsequently, we will utilize the VBA AutoFill method to propagate this formula to the last used row in Excel.

To achieve this, input 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

Explanation of key components:

- last_row = Cells(Rows.Count, 2).End(xlUp).Row: Determines the last used row in column B. You can adapt this to any column in your dataset.

- Range("E5").Formula = "=SUM(C5:D5)": Adds the sales of cells C5 and D5.

- Range("E5").AutoFill Destination:=Range("E5:E" & last_row): After obtaining the result, the AutoFill function starts from cell E5 and extends to the last used row obtained earlier.

Execute the VBA macro, and observe the resulting output:

As evident, our VBA code effectively sums the initial result and AutoFills the formula to the last row in Excel.

2. VBA AutoFill from ActiveCell to Last Row:

If you prefer not to specify a particular range in the AutoFill method and instead utilize the values, formulas, or formats of the active cell, this approach allows for seamless automation. This is particularly handy when you want to AutoFill the rest of the rows with the active cell's formula by simply clicking on a cell.

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

Let's illustrate this with an example of adding the sales of January and February, AutoFilling the formula to the last row using Excel VBA. Execute 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

Explanation of key components:

- last_row = Cells(Rows.Count, 2).End(xlUp).Row: Determines the last used row in column B. You can adapt this to any column in your dataset.

- ActiveCell.Formula = "=SUM(C5:D5)": Adds the sales of cells C5 and D5 to the selected cell.

- ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":E" & last_row): After obtaining the result, the AutoFill function starts from the active cell and extends to the last used row obtained earlier.

Here's a simple step-by-step guide:

1. Select Cell E5.

2. Run the VBA macro to add the formula and AutoFill it to the last row.

As illustrated, the VBA macro has successfully AutoFilled the formula to the last row, utilizing the active cell as the starting point.

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

In our previous discussions, we shared VBA macros for AutoFilling to the last row in Excel. However, in those examples, we exclusively employed dynamic ranges.

Let's examine a scenario involving static ranges for a better understanding:

Sub last_used_row()

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

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

End Sub

In this instance, we instructed Excel to input a formula in Cell E5 and AutoFill it from Cell E5 to Cell E11. Here, the range was explicitly specified. The drawback arises when additional rows are added; the VBA won't AutoFill those extra rows since we predefined a specific range.

To overcome this limitation, we've devised a solution that accommodates dynamic ranges:

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 illustrated, we first determine the last row using this line of code:

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

Subsequently, we AutoFill to the last row using the dynamic range:

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

This VBA code adeptly identifies the number of rows in your dataset, ensuring successful AutoFill with the formula, regardless of how many rows you add.

In essence, a dynamic range eliminates the need to manually input the range, allowing Excel to automatically update it as needed.

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

4. AutoFill to Last Column in Excel Using VBA:

If you've followed the earlier sections on AutoFilling to the last row, extending the functionality to the last column using Excel VBA is a straightforward process. By identifying the last column number, you can effortlessly AutoFill the formula.

Consider the provided dataset:

In this dataset, we're representing a 3-month budget. Our objective is to sum the expenses for all months and AutoFill the formula to the last column using Excel VBA.

Execute the following code:

Sub last_used_column()

Dim last_column 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

Explanation of key components:

- last_column = Cells(6, Columns.Count).End(xlToLeft).Column: Determines the last used column from row 6. You can customize the starting row based on your dataset.

- Range("D9").Formula = "=SUM(D6:D8)": Adds the expenses of three months (Jan, Feb, Mar).

- Range("D9").AutoFill Destination:=Range("D9", Cells(9, last_column)): After obtaining the result, the AutoFill function starts from column D and extends to the last used column identified by `last_column`. The main row is Row 9, containing all our data.

Upon running the macro, observe the resulting output:

The VBA has effectively AutoFilled the formula to the last column in Excel, demonstrating the seamless extension of functionality from rows to columns.

5.  AutoFill Sequential Numbers to Last Row in Excel with VBA:

You can effortlessly AutoFill sequential numbers in Excel using VBA, specifically by employing `xlFillSeries` as the AutoFill type. Excel intelligently recognizes the pattern and seamlessly populates the cells accordingly.

Consider the dataset presented in the following screenshot:

In this example, we have a list of names, and the goal is to assign sequential IDs to them using VBA AutoFill.

AutoFill Sequential Numbers with a Static Range:

Sub sequential_number_2()

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

End Sub

After executing the code, observe the output:

If you prefer a dynamic range, utilize 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

Here, 

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

Range("C5").AutoFill Destination:=Range("C5:C" & last_row), Type:=xlFillSeries: takes the ID from Cell C5 and AutoFills it to the last used row in Column C using `xlFillSeries`.

In both cases, the outcome remains consistent:

As demonstrated, the VBA code effectively AutoFills sequential numbers in Excel.

Download Practice Workbook

Autofill Formula to Last Row.xlsm

Conclusion:

In conclusion, this tutorial aims to equip you with valuable knowledge on AutoFilling formulas to the last row using VBA codes. We encourage you to practice these instructions on your dataset by downloading the provided workbook. Feel free to experiment and provide feedback in the comments. Your input motivates us to create more tutorials.

Embrace continuous learning and growth!