How to Find Last Row of Excel Worksheet in VBA Programming
Finding last row of spreadsheet dynamically is very important to identifying the address of the last row or column to perform mathematical calculations. When you writing a VBA code you don’t know how many rows with data in a spreadsheet particularly when data in rows or columns changed dynamically. To identifying the address of the last row or column is determined by count property. We apply two methods to identify last row of spreadsheet.
Method 1:
Determines last row number when the object encounters first empty cell in given column. It will ignore the subsequent data cells in that column(“A1”). For example
Private Sub CountMethod_1 ()
Dim Row_Count1 As Integer
Row_Count1 = Range("A1").End(xlDown).Row
UserForm1.Label1 = "Method 1-The last row with data is number: " & Row_Count1
End Sub
In this method (as shown in the image) it only counts six rows up to first empty cell encounters and ignores remaining data cells.
Method 2:
Determines last used row address irrespective of any number of empty cells in that column. So .row property will find the last used row in that column. As shown in image above finds the row # 16.
Private Sub CountMethod_2()
Dim Row_Count2 As Integer
Row_Count2 = Cells(Rows.Count, 1).End(xlUp).Row
UserForm1.Label2 = "Method 2-The last row with data is number: " & Row_Count2
End Sub
If you want to find last Column then replace the .row with .column and xlUp and xlDown with xlToRight of xlToLeft
Calculation of Sum: Example of Find Last row Implementation method
Private sub SumUpToLastRow()
Dim Row_Count2,I As Integer
Dim Sum as variant
Row_Count2 = Cells(Rows.Count, 1).End(xlUp).Row
For I = 2 To Row_Count2
Sum = Sum + Cells(I, 1)
Next
Cells(I + 1, 1) = Sum
End Sub
The result is “Sum = 8000011” in cell No 18
In the above example this code will first finds the address of last used row and then store the value in Row_Count2 variable. In For and Next loop it iterates upto the value stored in Row_Count2 variable and calculats the sum from row No 2 to 16. If in future the values filled in next rows even then this code will take care of it.
