 Hello everyone and welcome back to another tutorial. In this video, we're going to look at a method using Excel VBA we can use to find the last row in our data. So the purpose of this video is, again, it sort of follows on from the copy and paste that we looked at in our last video. If you haven't seen that, you can already check the link which will now be on the screen or alternatively there'll be other links during this video for other videos in our VBA series and even more importantly, the link to the playlist. And if you find that playlist amongst us on this channel, you'll find every one of our VBA tutorials in there. So let's say we're following on from the copy and paste and the purpose or the benefit of using this method we are to be using to find the last row is as we are all aware, data can obviously grow and shrink as we add more or less information to a particular list. By that you can see at the moment we've got eight rows of information, these names here down to row eight. And as with our previous video using copy and paste, we put in their hard values to do a copy and paste. So actually the best way to do that is just do a quick recap so you can show you what I mean. So I was going to insert a module to insert our code and we're going to add a subroutine called a copy, paste. And we'll do this. So the first thing we need to do is obviously define our sheet. So we go dim sheet one as worksheets. And hopefully you won't see me when I keep touching on this and I have a videos as being repetitive from other topics but hopefully by going over this as we're going to be doing it will help embed it into your memory and remind you how to do these certain parts. So we say we'll remember these bits of code. Then we're going to do obviously set sheet one equals, it's going to be sheets sheet one. Cool, so we've got a worksheet done there. So well first, well to look at one of the methods we used we would have gone, okay we'll say sheet one dot range and let's say it's a one through to a eight and we're going to go copy and we want to paste that in sheet one dot range and let's say go d, find letter d, d one like so. So if you now run this code, I'm going to hit F5. You can see it's copied all of our data across as desired basically as we are here. But because we've hard coded this range in it's not going to be any benefit to us as we add more data. So let me say, let's add another name in here so let's go like James and then let's rerun this code. Obviously it's only going to still copy up until row eight because okay, unless we go into the code here and change the a eight to a nine and rerun that it's obviously not going to update or pull through that additional information until we update it. So obviously it's got its limited use as we say. So therefore we need a method of adding into this code somewhere to be able to say what is this or a dynamic solution? So I say it's about to define what this row number we want to copy up until is. So in order to do that, we're going to enter this code as we're now going to quickly step through. So the first thing we need to do is we're going to add another variable to the mix. So we're just going to go up to the top here and I'm going to define another variable. So go dim, let's go R what could stand for row as integer. So we now need to define what the value of R is. So we could do at this moment, we could go R equals and we need number nine at the moment. And then what we can do is dynamically go into having just taken that comment off this row here. What we can do is we can go, get rid of the nine. So we can see that we're coming from A1 to A and then close quotes. If we then add an ampersand and then go R like so and then click off, you can see it's updated for us. So what it's going to do is because this piece of information here is a string, so it's the string what's feeding in the range that we want, we can actually obviously, we're now appending the value of R which is nine to the end of the string, what would actually form the value of A1 to A9 or whatever the value of R is. So if I run this code now, you can see it's identified that the value of R is nine. Therefore in our dynamic range here, we want to go from A1 to A9. And you can see it's worked perfectly and then copied over as required. If I remove this off here and this time this changed the value of R to say four and then rerun that, you can see that it's only now we're going to use the value of R being four. So it's only therefore going to go down to the range of A1 to A4 and that's exactly what is done here. So that's the first part of what we need to do completed. But obviously we need some, we need a way of obviously making our more dynamic than obviously as we're doing at the moment. So in order to do that, what we can do is there's a couple of methods we could use for this really. But the first one, or the main one we're going to look at this video is how to look at that last row. And in essence, what we're going to have the VBA macro do is the same thing as us being in cell A1 here, holding down the control button and then pushing the down arrow. So many of you will be aware that that shortcut or that is a valid shortcut in Excel where if you hold down the control button and you can then use your arrows on the keyboard to either go to the last column on the right in this example, go left to find the first, you can go up to find the first row or go right to the top of the sheet really or you can go controlled down to give you the last value in that list. Obviously, if I was going down here again, it'd take me all the way to the end of the workbook. If I go up, it takes me to the last row of our data and then once again to the top. So you're able to navigate those shortcuts using the control button and the arrows. And that's in essence, what we want our VBA code to do here. We want it to say, okay, what is the last row? Do that count how many rows there are and then okay, whatever that value is is what we want to use. So how do we enter that code? Well, the first thing we need to do is first, as always with our sheet is reference the sheet we have. So what sheet one. And the first part we then want to do is go sheet one dot cells. So obviously we're referencing to cells within this workbook, so it's open brackets. And as you can see, it's now asked us to do a row index and a column index. And we sort of jumped over that but obviously we've been referring to cells. We've obviously used either the range method or we've been using the cells method. And the cells method allows us just to provide a numerical value that references that either the row or the column. So we know that we need to do the row number first and then the column index or column number second. So for the first part in terms of the row and this is where we're now going to define obviously what row we want to look at in this range. So the first thing we just do is go sheet one dot rows, dot count and it seems quite logical and that is the first part we have to do there. And then we can do comma and then we've done that rows part. And what this is basically saying is go into sheet one, count all of the rows in sheet one, reference all the rows and just count how many rows have got a value in them. So in essence, all it's gonna do is go down this column here and count how many rows have got detailed in them. And then the second part we then need to do here is column index and we're just gonna reference column A like so, so it's easy and clear to reference. Close brackets. Next thing we want to do is go dot end because we want to go to the end of the range but we're also then gonna have to do this added part here what says XL up. Close brackets, do dot row and hit enter and you can see it's formatted for us. So what this is basically saying is we want Excel to go down to the last row and what it's actually gonna do is it's gonna go past. So it's gonna go to the next available row and the end part of the XL up is literally gonna just say, okay, we'll go up to the next row what actually has got data in it. So rather go to the next empty cell where you want to go to the next or the last row that has information in it. And for us, it's obviously gonna take us to row nine. So all we need to do now we've actually already put our in here. So we can now quickly test this out. So this clear column D. So if we now run our code and we could just tidy this up over so slightly like so. So if we now run this, you can see as it appears it's copied all the information that we require. So it looks like it's actually worked. If we had to again clear D and let's say let's take off Jenny and James. So we're only going down to Josh at the moment and let's copy that across. So let's go run that. You can see it's only now gone down to Josh. If we add into a Jenny and James and now rerun this code you can see it's now also picked up Jenny and James for us as well. So that is the piece of code we're gonna use to be able to find that last row. And it might seem fairly straightforward and obvious but I've also just done another second sheet here to show you how you can obviously implement this code when you've got more than one column of information. Very simple, all it requires us to do and actually all I'm gonna do is copy this over to that first sheet rather than rewrite all this code. So let's just remove that and remove James. So you can now see that we've actually got a name that we started with and we've also added in what their age is. So all we need you to do now is if you were to run this piece of code at the moment you can see it already copies the name for us and I accurately counts how many rows of information we need to count, let's clear that. All we need to do to now enable our copy to expand the whole range of this table or the two columns is simply come into our copy part here and all we're gonna do is where it says A to A1 that's still the same starting point but we now want to go to column B. So let's just change this to B so you can see what A1 to B and obviously whatever the updated row number is gonna be and now when we hit F5 you can see it's copied all of that information for us as well and if we were to change this information go James and let's say James is 22 and run that once again. You can see it's now also picked up that last value of James and added that to our desired location as well. So we hope you enjoyed that video. Hopefully it wasn't too rushed and it did make all good sense. If you do however have any questions please do drop me a comment below this video and I'll get back to you as soon as I can. If you did enjoy the video it was helpful. It taught you something new or actually it answered a question you were potentially searching for. Please do give the video a like. It's not only greatly appreciated by myself but it also does help out all important YouTube algorithm. And lastly if this is your first time finding one of our videos or you have watched our videos previously in the past please do subscribe to the channel and also make sure you hit that bell notification button so you are notified of all of our future videos. And so lastly thank you again for watching and we'll see you in the next video.