Find last row with data in excel using VBA

In this post, we will find last row with data in excel using VBA. This piece of code is very useful when we need to automate certain functions in excel, such as appending data of same format from multiple sheets into one sheet. Consider the following table in excel sheet:

Sample table in Excel

The function should return 16, since the 16th row is the last row with data in it. Let us see how VBA code can help us find the last row. Consider the procedure given below:

On running this procedure, the output will be number of rows = 16. Let us understand how this procedure works. First, we find the number of rows in the worksheet using Rows.Count . From the last cell in first column, we move up using the End(xlUp) property. Thus, this works as Ctrl + Up key and stops at first non-empty cell. It then simply returns the row of that cell using Row property.


This function can also be extended to find the last column containing data. Consider the procedure given below:

Let us understand how this procedure works. First, we find the number of columns in the worksheet using Columns.Count . From the last cell in first row, we move up using the End(xlToLeft) property. Thus, this works as Ctrl + Left key and stops at first non-empty cell. It then simply returns the column of that cell using Column property. On running this procedure, the output will be number of columns = 4.

References: Microsoft Office VBA Reference
1. https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-end-property-excel
2. https://msdn.microsoft.com/en-us/vba/vba-excel