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.
