The VBA Dynamic Range provides you the opportunity to use the same code for newly added or removed data while working with a larger dataset. However, it will be handy if you add the last row with the dynamic range. In this article, I’ll discuss 3 methods on how to use the VBA dynamic range in the last row in Excel with proper explanation.

## Basics about Dynamic Range for Last Row

### What is Dynamic Range for Last Row

Most likely, you might have heard about the application of the VBA Dynamic Range in Excel. This tool provides the advantage of expanding and shrinking the range automatically when you add or remove the entry. Luckily, you may apply the tool while working in Excel VBA.

Besides, the Finding Last Row in VBA is quite important particularly if you have a complex dataset.

What if you apply the dynamic range in the last row in Excel VBA? Truly, it works well if you want to apply it to a complex dataset.

### Why Do You Need Dynamic Range

Let’s introduce today’s dataset to understand the distinction of the dynamic range in the last row. Here, Quantity, Unit Price, and Sales are provided for each Item. Now, you want to find the average, sum, maximum value, or something like that type of calculation.

Let’s say, you want to compute the average value of the *Sales*. However, you must insert a module to enter the VBA code.

Firstly, open a module by clicking **Developer **> **Visual** **Basic**.

Secondly, go to **Insert **> **Module**.

Then copy the following code into the newly created module.

```
Sub Average_without_DynamicRangeLastRow()
Range("G5").Value = Application.WorksheetFunction.Average(Range("E5:E12"))
End Sub
```

In the above code, I used the **Range.Value** property to define the output cell. Besides, I applied **WorksheetFunction.Average** for computing the average of the *Sales*. Lastly, the input cell range is specified by the Range object.

Next, if you run the code (the keyboard shortcut is **F5 **or **Fn** + **F5**), you’ll see the following output.

But you may need to add a new entry e.g. **B13:E15** cell range.

Now, what should you do to include these new entries in the average?

If you use the traditional method (as shown in the earlier code), you’ll not get your desired output unless you specify the cell range again. Undoubtedly, it is a tedious task especially when your dataset is larger.

Fortunately, you may find the average of the existing dataset and the dataset with new entries using the same code. In that case, you have to utilize the dynamic range in the last row.

## Excel VBA Dynamic Range for Last Row: 3 Methods to Find and Use

In this section, I’ll discuss the ways to find the last row and then apply dynamic range in the case of some real-life examples.

Let’s dive into the methods.

### 1. Using the Range Object

In the beginning method, you’ll see how to find the last row using the **Range** object. Enjoy the method in a step-by-step process.

__Step 01: Finding the Last Row__

To get the number of the last row in the case of your dataset, just use the following code.

```
Sub Dynamic_Last_Row_Method1()
Dim LRow As Long
LRow = Range("E:E").SpecialCells(xlCellTypeLastCell).Row
MsgBox "Last Used Row Number is: " & LRow
End Sub
```

In the above code, I declared the **LRow **(short form of the last row) as a **Long** data type first. Then I defined the **LRow **with the **Range **(“**E:E**”) and SpecialCells method which would return the last cell. Then I utilized the **Row** property to get the row number. Finally, a **MsgBox **is added to display the last row number.

If you run the code, you’ll get the output is **12**.

*Note**: I used column E but you may insert any other column based on your requirement.*

__Step 02: Computing Average__

Now let’s come to the important thing i.e. use the dynamic range in the last row.

Just copy the following code to compute the average of the *Sales*.

```
Sub Average_Method1()
Dim LRow As Long
LRow = Range("E:E").SpecialCells(xlCellTypeLastCell).Row
Range("G5").Value = Application.WorksheetFunction.Average(Range("E5:E" & LRow))
End Sub
```

In the above code, I declared the **LRow **and defined its value. More importantly, I added Column **E **(which holds the value of the *Sales*) along with the **LRow **to get the average of the *Sales *field in the **G5 **cell of the sheet.

*Note:** Remember this same code will work for calculating the average no matter if you add new data or remove any data from the existing dataset.*

After running the code, you’ll get the average of the *Sales *is $4443.75.

__Step 03: Dealing with Newly Added Data__

Assuming that you have to add new entries e.g. **B13:E15** cells. Obviously, the row number will be increased. To check the row number, run the same code (discussed in **Step 1**). So the changed row number is **15**.

Whatever, if you want to get the average of the Sales with new entries, just run the same **VBA **code (discussed in **Step 2**). Shortly, you’ll see that the average is updated automatically and it is $3768.18.

Thus the dynamic range in the last row works efficiently.

**Read More:** Excel VBA: Copy Dynamic Range to Another Workbook

### 2. Applying the UsedRange Property

In the second method, I’ll show the process of finding the last row by applying the **UsedRange** property and then applying it with a dynamic range for calculating the sum of the *Quantity*.

__Step 01: Computing Sum in the Usual Way__

To compute the sum for a cell range, maximum people use the following code.

```
Sub Sum_Quantity_without_DynamicRangeLastRow()
Range("G5").Value = WorksheetFunction.Sum(Range("C5:C12"))
End Sub
```

In the above code, I used the **WorksheetFunction.Sum** method with defining the **C5:C12** cell range. However the cell range is not dynamic. That means you have to change the cell range if you add new data to the dataset.

However, if you run the code, you’ll get the output is 102.

__Step 02: Finding the Last Row__

Applying the **UsedRange** property, you can easily get the last row number. Just copy the following code into the newly created module.

```
Sub Dynamic_LastRow_Method2()
Dim LRow As Long
LRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
MsgBox "Last Used Row Number is: " & LRow
End Sub
```

In the above code, I defined the **LRow **with the **UsedRange **which displays the last used range on a certain worksheet. Then **Rows.Count** counts the total number of rows in the active worksheet.

After running the code, the output will be 12.

__Step 03: Dealing with Newly Added Data and Computing the Sum__

Let’s say, you want to add 4 new rows (**B13:E16** cell range). After adding the new rows, if you run the same code mentioned in the previous step, you’ll get the changed row number is **16**.

But if you want to calculate the sum of the *Quantity *with the dynamic range in the last row, just copy the following code.

```
Sub Sum_Method2()
Dim LRow As Long
LRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
Range("G5").Value = Application.WorksheetFunction.Sum(Range("C5:C" & LRow))
End Sub
```

Here, I specified the *Quantity *field (column **C**) with the dynamic **LRow**.

You’ll get the sum of the *Quantity *is 182 in a moment if you run the code.

Needless to say the code will work dynamically and you don’t need to make any changes to the code.

### 3. Utilizing the Cells Property

Lastly, you may find the last row utilizing the **Cells** property if you want.

__Step 01: Finding the Last Row__

Initially copy the following code to find the last row using this method.

```
Sub Dynamic_LastRow_Method3()
Dim LRow As Long
LRow = Cells(Rows.Count, 4).End(xlUp).Row
MsgBox "Last Used Row Number is: " & LRow
End Sub
```

In the above code, I assigned **Rows.Count** and the value of **4 **(the column number is of column **D**) in the **Cells **property. That means it counts all the row numbers of column **D**. Later, I used the **End** property with the **xlUp **snippet which finds the last used row.

After running the code, you’ll get the row number of the last row is **12**.

__Step 02: Working with the Dynamic Range in the Last Row__

Now, you may add the new data as shown in the following image.

Instead of calculating the average and sum, you may find the maximum value of the *Unit Price*.

To accomplish the task with dynamic range in the last row, just copy the following code.

```
Sub Max_Price_Method3()
Dim LRow As Long
LRow = Cells(Rows.Count, 4).End(xlUp).Row
Range("G5").Value = Application.WorksheetFunction.Max(Range("D5:D" & LRow))
End Sub
```

In the above code, I utilized the **WorksheetFunction.Max** method to return the maximum value in column **D**.

If you run the code, you’ll find the maximum unit price is $1000.

**Read More:** Excel VBA: Dynamic Range Based on Cell Value

**Download Practice Workbook**

## Conclusion

That’s the end of today’s session. I strongly believe from now you may execute the calculation using the dynamic range in the last row. Anyway, if you have any queries or recommendations, please share them in the comments section below.

Get FREE Advanced Excel Exercises with Solutions!