Find The Last Occupied Row

You can use the following code to find the last row in a worksheet that contains data and assign it to a variable.
If you use or are planning to use VBA frequently then it would be a good idea to memorize this line of code because you will use it in many different situations.

Cells(Rows.Count, 1).End(xlUp).Row

The code we will use in this example will reference to a variable.
LR = Cells(Rows.Count, 1).End(xlUp).Row

Lets break the code down into four sections.

LR

LR is the variable we are using you can change this if required.

Cells(Rows.Count,1)
Count the total number of rows in the worksheet (Rows.Count) in column 1 (,1)

VBA Last Row1

The Rows.Count allows the code to be used in different versions of Excel.

e.g. If we hard coded this to work in Excel 2013 it could read
Cells(1048576,1)
but if we used this in Excel 2003 it would error because Excel 2003 only has 65536 rows.

End(xlUp).Row
This is equivalent to holding the Ctrl button and pressing the up arrow in Excel.
In this example it will return the row number 12.
VBA Last Row3

If your code required you to write to the next available row you can just add + 1 to the end of the code.
If this was applied our variable LR would equal 13 which in our example would be the next available row to add data.




Be the first to comment

Leave a Reply