Finding the Last Row Number in VBA

When you try to write a Sub-Procedure, which needs to manipulate the data and the data doesn’t have a pre-defined number of rows, it is very important to find the appropriate way of discovering the last row number of the table you are working on.

Or you might want to get 2 worksheets, which have the same layout, together. You will need to find the first available row to be able to append the data to the first worksheet. In such a situation, you will need to find the last row and add one to get the first available row number.

No matter what, finding the last row number is one of the most basic knowledge that you need to learn to be able to write functional codes. I will list here 5 different ways of finding the last row in VBA, however, please make sure that your data matches to the method you are using. Otherwise, you might get unexpected results.

Going from top to bottom

Dim lastRow As Integer
lastRow = Range("A4").End(xlDown).Row

In case you are sure that there is no break within your table up to bottom (I mean empty row or cell), you can use the End (xlDown) method to go from top to bottom and assign the number of the last row to the variable named lastRow.

Going from bottom to top

Dim lastRow As Integer
lastRow = Range("A" & Rows.Count).End(xlUp).Row

As you may see from the method, you first go to the very bottom of the Excel Worksheet. And from there you go up to the first cell, which has value inside. In this scenario, please make sure that there is no table, value provided between the bottom and the last row number you plan to have.

Current Region

Dim lastRowOfRegion As Integer
Dim firstRow As Integer
Dim lastRow As Integer
lastRowOfRegion = Range("A4").CurrentRegion.Rows.Count
lastRow = firstRow + lastRowOfRegion - 1

In case you have a table to which the Range you mention inside of the code belongs, you can use the last row within the Current Region. However, this method will give you the number of rows within the region but not the row number of the last row. This method is more useful when you have a table and you want to create a two-dimensional Array.

Used Range

Dim lastRowOfUsedRange As Integer
Dim firstRow As Integer
Dim lastRow As Integer
lastRowOfUsedRange = Application.ActiveSheet.UsedRange.Rows.Count
lastRow = firstRow + lastRowOfRegion - 1

Used Range is also similar to the Current Region. You will not find the row number of the last row but the number of rows available in the table. In specific situations, it might be very useful to find the used rows number, and using this information you can find the row number of the last row in the used range.

Specialcells

Dim lastRow As Integer
lastRow = Cells.SpecialCells(xlCellTypeLastcell).Row

Although I mentioned here 5 different ways, my favorite one is finding the last row looking from bottom to top. Because I always work with a set of data, which is generated from the systems and there is almost no chance that there is going to be a cell with a value between my last row and bottom of the worksheet. But for such a situation, you need to know exactly what the first-row number is. Since it is fixed within the report type, you can work on the data starting from the pre-defined first-row number and the last row number that you find using any of the methods above.

In case you have any questions regarding your specific problem, please do not hesitate to leave a comment under the post or simply contact me. Two heads are definitely better than one.

It is also possible that there are some other ways I didn’t mention in this post. If you have any specific method you are using other than the ones I mentioned, please let me know. I am always open to new methods. New problems raise a need to apply different approaches to optimize the solution so please share with us your ways of finding the last row!

Source: https://www.ozan.pl/blog/post/4