Learn How to Extend Formula to the Last Row in Excel Formula with VBA in 3 Easy Steps

One of the common tasks in Excel is to apply a formula to a range of cells. For example, you may want to calculate the sum, average, or percentage of a column of data. However, sometimes you may not know the exact number of rows in your data, or the number of rows may change dynamically. In such cases, you may want to use VBA to extend the formula to the last row in the column, regardless of how many rows there are.

In this article, we will explain how to use VBA to extend a formula to the last row in excel formula. We will cover the basic theory, the procedures, and a comprehensive explanation including the basics. We will also provide a scenario to give a detailed example with real data, and show the calculation and the result using an excel table. Finally, we will discuss some other approaches that you can use to achieve the same goal.

The basic idea of using VBA to extend a formula to the last row in excel formula is to use the Range.AutoFill method. This method allows you to copy the formula from a source cell or range to a destination range, and fill the destination range with the values or formulas based on the source pattern. The syntax of the Range.AutoFill method is as follows:

Range.AutoFill Destination, Type

Here, Range is the source cell or range that contains the formula that you want to extend. Destination is the destination range that you want to fill with the formula. Type is an optional argument that specifies the type of fill. There are various types of fill that you can choose, such as xlFillDefault, xlFillCopy, xlFillSeries, xlFillValues, etc. You can find the full list of fill types here.

Procedures

To use VBA to extend a formula to the last row in excel formula, you need to follow these steps:

1. Identify the source cell or range that contains the formula that you want to extend. For example, if you have a formula in cell B2 that you want to extend, you can use Range("B2") as the source range.
2. Identify the destination range that you want to fill with the formula. To do this, you need to find the last row in the column that contains the data. You can use the Cells(Rows.Count, column).End(xlUp).Row method to find the last row in a given column. For example, if you want to fill the formula in column B, you can use Cells(Rows.Count, 2).End(xlUp).Row to find the last row in column B. Then, you can use Range("B2:B" & lastrow) as the destination range, where lastrow is the variable that stores the last row number.
3. Use the Range.AutoFill method to copy the formula from the source range to the destination range. You can specify the type of fill as an optional argument, or use the default type if you omit it. For example, you can use Range("B2").AutoFill Destination:=Range("B2:B" & lastrow), Type:=xlFillDefault to fill the formula in column B to the last row.

Explanation

To illustrate how to use VBA to extend a formula to the last row in excel formula, we will use a simple example with real data. Suppose you have a table of sales data for different products in different months, as shown below:

You want to calculate the total sales for each product by adding the sales from Jan to Jun, and put the formula in column H. However, you may not know how many products there are in the table, or the number of products may change in the future. Therefore, you want to use VBA to extend the formula to the last row in column H, so that the formula can adjust to the number of products automatically.

To do this, you can use the following VBA code:

Sub ExtendFormula()
    'Declare a variable to store the last row number
    Dim lastrow As Long
    
    'Find the last row in column A
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Enter the formula in cell H2
    Range("H2").Formula = "=SUM(B2:G2)"
    
    'Extend the formula to the last row in column H
    Range("H2").AutoFill Destination:=Range("H2:H" & lastrow), Type:=xlFillDefault
End Sub

Let’s explain the code step by step:

• First, we declare a variable lastrow to store the last row number in the table. We use the Long data type to avoid the overflow error if the number of rows exceeds the limit of the Integer data type.
• Next, we find the last row in column A, which contains the product names. We use the Cells(Rows.Count, 1).End(xlUp).Row method to find the last non-empty cell in column A, and assign its row number to the lastrow variable. Note that we use 1 as the column index for column A, and Rows.Count as the total number of rows in the worksheet.
• Then, we enter the formula in cell H2, which is the first cell that we want to fill with the formula. We use the Range("H2").Formula property to assign the formula to the cell. The formula is =SUM(B2:G2), which calculates the sum of the sales from Jan to Jun for the first product.
• Finally, we extend the formula to the last row in column H, which is the column that we want to fill with the formula. We use the Range("H2").AutoFill method to copy the formula from cell H2 to the destination range, which is Range("H2:H" & lastrow). This range starts from cell H2 and ends at cell H concatenated with the lastrow variable. For example, if the last row is 6, the destination range will be Range("H2:H6"). We also specify the type of fill as xlFillDefault, which means that the formula will be filled according to the source pattern.

After running the VBA code, the table will look like this:

As you can see, the formula has been extended to the last row in column H, and the total sales for each product have been calculated correctly.

Other approaches

Using VBA to extend a formula to the last row in excel formula is one of the possible approaches, but not the only one. There are some other ways that you can use to achieve the same goal, such as:

• Using the Range.FillDown method: This method copies the formula from the first cell in the range to the rest of the cells in the same column. For example, you can use Range("H2:H" & lastrow).FillDown to fill the formula from cell H2 to the last row in column H.
• Using the Range.FormulaR1C1 property: This property allows you to assign a formula to a range of cells using the R1C1 notation, which uses relative references based on the row and column numbers. For example, you can use Range("H2:H" & lastrow).FormulaR1C1 = "=SUM(RC[-6]:RC[-1])" to fill the formula in column H using relative references.
• Using the Range.FormulaArray property: This property allows you to assign an array formula to a range of cells. An array formula can perform multiple calculations on a range of cells and return an array of results. For example, you can use Range("H2:H" & lastrow).FormulaArray = "=SUM(B2:G2)" to fill the formula in column H using an array formula.

These are some of the alternative approaches that you can use to extend a formula to the last row in excel formula. You can choose the one that suits your needs and preferences best.