In this video, we're going to speak about how you can find the last row on your data set using Excel VBA. So finding the last row on your data set using Excel VBA is very important because if you're dealing with a data set like this using VBA code, and let's say you're copying good data, you need to make sure that your code can determine what the last row for the data is, regardless of how many rows the data has. So if for example, the next time you run the macro on this data set, instead of having 18 rows, it would have 30 rows, for example, you need to make sure that you have code that can determine each time how many rows there are on your data set so as to only select the rows that contain the data set, and you wouldn't select extra rows that are actually blank.

Okay, so this is something that is very important to know why you're going to use it on nearly every time time you write a macro that deals with a dataset. Okay, so finding your last row actually involves three steps. The first step is actually starting at the last row in Excel. So actually, we can get to the last row in Excel by pressing Ctrl and down. But this does not happen on the code on the macro, the macro just starts at the last row. Okay, so this is the starting point of your macro.

And then excel vba actually jumps up until it hits the last row on your data set. And you can jump up actually by pressing Ctrl and up and this is actually what the code does. So it starts at the last row and the code you can think of it as pressing CTRL and up till it hits the last row on your dataset, which here in our case is going to be row number 18. And then the third step is asking what row on my on? Okay, so this is actually the third step. Step One Pro on my own.

And of course, these three steps actually happen in just one line of code. Okay, but this is how you can understand it, just go to the last row, jump up until you hit the last row on my data set, and then ask the question, which row on my on? Okay, so I've opened the Visual Basic Editor here. And basically, in order to determine the last row on your data set, you need to create a variable because this variable is going to contain the row number for the last row. And the row number is going to be basically an integer, right? It's just a number, and it cannot be a decimal.

So I'm assigning to it the integer data type. And also the single data type can work because the single data type can work with both integers and decimals as well. And if your data set contains more than 32,000 rows, then this integer data type will not be enough. Right. So you need to go higher go single, go double whatever. So the last row, actually, you're going to determine it using the statement, cells rows dot count, and then comma one, and then dot end, Excel up dot row.

Okay, so that's analyze that statement and see what it does. Basically, I'm using the cells collection object to go to a cell. This cells address, the row number is going to be rows count, which is actually the number of rows in my Excel workbook. And we're writing it as rows dot count, instead of writing 1 million something because first of all, it's easier to write it this way, because you don't have to memorize the number. And the second thing is that if you're using Excel 2003, Excel 2003 only had 65,000 rows. So this ensures that you select the last row on your worksheet regardless The number of rows because you're actually using the count property for the rows collection object to determine the number of rows.

And then this is the column number for the cell that we're going to start from. And the column number here is one. And it could be anything because this will depend on your data set. In our case here, I'm actually going to assume that the name column is the one that always has values because the column that you base your last row on should be the column that always has values. Like for example, the employee ID, and employee couldn't not have a name on the record or the date of birth, but he can always have an ID if that's how your company works. For example, here, there will always be a name, for example, in this data set, but there could be a row where there's no date or there is no item sold or so.

But this data set can always contain the name so you need to make sure to base your row counting on the column. That you suspect that he will always have data, okay, or that you think that it will always have data in it. So this is the first step, I'm starting on the last row in Excel. And then dot and excel up. This is basically pressing Ctrl N up. Okay, so this is the second step that I'm doing.

So the first step is starting in the last cell in Excel. Second step here is pressing Ctrl N up. And when I do that, I'm hitting the last row on my data set. So the third step is asking for the row number with the dot row property. So cells, rows dot count, comma, one dot end Excel up. So I'm on the last row in Excel, pressing CTRL and up, and then I'm hitting a cell and then I'm asking for the row property, which will give me the road number for that particular cell, which in our case, should be 18.

So if we step through this code step by step, using f8 on our keyboard, when we execute this line of code, you can see our LR is equal to 18. And that means the our last row is row number 18, which is correct if let's say we've added any rows to this dataset. So now we've got one extra row and we're run this code again. So let's run it again step by step as well. You can see our the LR is has become 19. So that means that when you put this line of code in your code, this will dynamically determine what the last row on your data set is.

So if your data set expands, so number of rows increases or shrinks, number of rows decreases. You will always make sure that your macro will be able to determine how many rows your data set has. That is actually a very Important thing to learn in VBA. And you need to actually memorize this statement, just as you memorize your name. Okay, this is a very important statement that you're going to use a lot. There is another way that you can determine what the last row is, although it's not recommended, but I'll just explain it for you.

Basically, you can start at any cell in your data set. And if you press Ctrl, and down, let's see what happens when you press Ctrl and down, you actually end up on the last non blank cell, okay, it's not going to be essentially the song the last row because if there is a blank cell in the middle, you're going to end up on it. So this is why I'm not recommending it. For example, if we insert a row here, and this row is blank, and if I press Ctrl and down, you can see here I've stopped actually the last non blank row but there is a row here that is blank. And this is not my last row. So this is why this way is not recommended.

Because if you press Ctrl and down and write the code this way, and this way is actually cells one comma one, or it could be any cell in your data set dot end Excel down, and then the Excel means press Ctrl and down, and then ask for the row number. If there's a blank cell, you're not going to reach your last row. And you could end up with having the last row to be incorrect. This is why you're not going to see the second way that we've explained here being used often and you're actually going to see the first way of doing it being used more often. So rows dot count, comma one dot and excel up dot row. So that's it, guys for finding the last row in Excel VBA.

It's a very important thing to understand. Thank you very much watching this video and I'll see you on the next one.