How to Use Dynamic Range for Last Row with VBA in Excel (3 Methods)

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.


Download Practice Workbook


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

Dataset

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.

How to Insert VBA Code

Secondly, go to Insert > Module.

How to Insert VBA Code

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

Finding the Average in the Usual Way

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.

Average of the Sales

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.

Average of the Sales

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


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

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

Excel VBA Dynamic Range Last Row Using the Range Object

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.

Excel VBA Dynamic Range Last Row Using the Range Object

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 for computing 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

Excel VBA Dynamic Range Last Row Using the Range Object

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

Excel VBA Dynamic Range Last Row Using the Range Object

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 Step1). So the changed row number is 15.

Excel VBA Dynamic Range Last Row Using the Range Object

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.

Excel VBA Dynamic Range Last Row Using the Range Object

Thus the dynamic range in the last row works efficiently.

Read More: OFFSET Function to Create & Use Dynamic Range in Excel


Similar Readings


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

Excel VBA Dynamic Range Last Row Applying the UsedRange Property

In the above code, I used the WorksheetFunction.Sum method with defining the C5:C12 cell range. But 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.

Excel VBA Dynamic Range Last Row Applying the UsedRange Property

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

Excel VBA Dynamic Range Last Row Applying the UsedRange Property

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.

Excel VBA Dynamic Range Last Row Applying the UsedRange Property

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.

Excel VBA Dynamic Range Last Row Applying the UsedRange Property

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

Excel VBA Dynamic Range Last Row Applying the UsedRange Property

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.

Excel VBA Dynamic Range Last Row Applying the UsedRange Property

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

Read More: Create Dynamic Sum Range Based on Cell Value in Excel (4 Ways)


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

Excel VBA Dynamic Range Last Row Utilizing the Cells Property

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.

Utilizing the Cells Property

Step 02: Working with the Dynamic Range in the Last Row

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

Utilizing the Cells Property

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

Excel VBA Dynamic Range Last Row Utilizing the Cells PropertyIn 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.

Utilizing the Cells Property

Read More: Excel VBA: Dynamic Range Based on Cell Value (3 Methods)


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


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo