How to get the Last Row in VBA(The Right Way!)

if you've been using vba for any length of time then no doubt you're using some edit to get the last row last column or last cell but most likely the metadata you're using is wrong i'm going to look at five methods in this video for getting the last row last column and last cell and i'm going to show you why four of them have very big shortcomings and then at the end of the video i'm going to give you some of my own functions to make your life much easier when it comes to getting the last row cell and column so let's go ahead and get started if you like this video then please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the bell icon beside it the first method we're going to look at is current region so what current region does is it gives us back all the adjacent data so if we click here and we press control asterisks or control shift and 8 on most keyboards it gives us all the data that's adjacent so the current region ends when it reaches a blank row or a blank column so if we put data in column f and we put more data in row 13 what you'll see is that when we select current region again that it doesn't reach this data because it stops at the blank rows and columns now when we put some data in row 12 and we do current region you can see that it reached row 13. so current region is very useful when we have records adjacent like this and we want to get the entire range back when we're writing the code current region is very useful because it's very easy to use so here we're declaring our range and we're going to set the range equal to we get the worksheet object and we then just pick the range which is a cell within the data we want and then we say current region and this returns us a range of the current region so let's select this range and we'll run the code just to see how it works you can see here that it selected the current region and all the data was selected now if we want to get the last row or the last column we can do it the same way for any range so once we have the range what we do is we use the range rows collection so the range rows collection gives us back all the rows in a range and we can specify the one we want now the one we want is the last one so it's the count of rows that we want to get back and this will give us back the last row and we want a row number so we can just say dot row and that gives us back the row number of the last row now let's copy the code here and we're just going to select this just to show that it is indeed the last row so now we run this code you can see that we got back the last row now if we want to get back to last column we basically just take this exact same code and replace every instance of row with column and then the code will give us back the last column now we run this chord and you'll see it gave us back the last column now the advantages of the current region gets to range easily but the disadvantages is that it doesn't work on a protected sheet and the data must be adjacent so if it's jagged data that we're dealing with then current region will not work so if we look at this data here we obviously can't use current region and the reason for this is because we've got a blank row we've got some blank columns so current region will only return partial data now what we can use is used range so the way used range works is it returns the entire range for a worksheet it's actually a method of a worksheet object so let's write the code we set this range variable and it's going to equal the worksheet object and then as i said we just take a method of the worksheet object which is used range and that's how simple it is to get back to used range now let's select the whole range now that we've got it back and run the code and you can see that's our used range selected so at first lance use range seems quite useful now if we want to get back the last row or the last column it's very similar to how we did it with current region we simply use the range and range count to give us the last row so here we're going to get the last row and then we're going to highlight it so when we run this code what you'll see is that it highlighted the last row if we want to get the last column then we use the same last column code that we use for current region so we run this code and you'll see that it gave us column h as the last column at first glance used range seems like a really useful method but it's got one major drawback and let's illustrate it here let's change the background color to yellow on this cell now when we run the code again and we're just going to run the code to give us back the entire range and we're going to select that range so let's run this code and you can see that now to use range includes the format itself so use range includes any cells that are formatted so this causes us a bit of a problem because if we want to get back just the data and we don't care about the format itself it doesn't matter use range is going to give us back the entire range anyway so this is one of the major drawbacks of the used range so to use range very good for getting the range and it works with a protected sheet but unfortunately it includes formatted cells as well which makes it very awkward to use with data and you cannot specify a section of the worksheet it's basically all the worksheet so next we're going to look at special cells which is a function of range a quick pause to tell you about the excel vba handbook course are you struggling to build vba applications do you find it difficult to get good information on how to create real-world vba code is it a struggle every time you try to create a vba application no matter how simple it is well the excel vba handbook course teaches how to build real-world excel vba applications from scratch unlike most courses you won't be overwhelmed with information and left to figure out how to put it all together instead you'll be taken step by step to 10 excel vba applications with every concept explained once you start working through vba applications you'll be amazed how quickly your vba skills increase so check out the vba handbook course at the excelvbahanbook.com and the link can also be found in the description below the video so here we use the worksheet object we're going to say cells which basically means the entire range and then we're going to use special cells and the cell type is going to be last cell and when we run this code what you'll see is that it gives us back cell h9 so it actually gives us back the cell now this seems quite promising and if we want to get the last row or the last column then it's very easy we just just basically use the row number or the column number from this cell you can see the last row is 9 and the last column is 8. now if we want to get the entire range then we have to build up the range but it's not really too complicated so declare our variable as range full and then we set this to be a range on our worksheet and that range is going to start at cell 1 1 and it's going to end at our last cell which is the cell that we've just got so we just do cells one one here and then we just do range which is our last cell so we run this code what you'll see is that it highlights our entire range so this all looks very good but select has a couple of problems the first one is again the formatting problem if we format a cell and then run our code then specialcells considers this to be a last cell now the problem as well is that if we delete this row and we run the code then special cells doesn't realize that this has been deleted and the only way to do it is basically to close our workbook save our workbook or run used range so this means really that special cells is basically unusable so we'll have to look at a better method for getting the last rows and last cells in jacket data so special cells initially they do seem to work well with jagged data and they get the last cell which is very useful however the problem is formatted cells are included when they're searching for the last cell and it doesn't work on a protected sheet we're going to have to go and find something a bit better so this method end excel up is one of the most common methods for getting the last row but it does have some weaknesses now how it works is as follows we basically get the last row in the spreadsheet so we do it like this we do sheet data rows count and this will give us the very last cell so let's have a look at it on the spreadsheet this will give us this cell here and if we press control up arrow it brings us up to the last cell with data and this is essentially what we're doing here we use end excel up and it gives us the last cell which will be a11 in this case and then we get the role from that so let's run the code which will select the last row and you can see that it selected row 11. now if we want to get the last column we're basically just using the same code again but we're going to replace it with column so everywhere row we're going to replace with column and we just have to make one or two other changes as well so in this case we're going to start at the last column rather than at the last row so we do row one and then it'll be the last column and instead of excel up because we're not moving up it's xl2 left and this will give us the last column works the same way as we did for row so when we run this code you'll see that it selected the last column building the range with this method requires a little bit more code than the other methods we've got to set the range equal to and we use the sheet name and then arrange on the sheet we get basically the starting cell and the end cell now because i'm using sheet data a few times here i'm going to make it into a wheat statement and then we'll use cells as the starting point and that's a1 so cells one one is a1 and the last cell will be our last row and last column and this will give us the range and then we'll close our end width statement and we'll select this range that we've got so when we run the code you'll see that it gives us back the range as we expected now the problem with this method is that it doesn't work very well with our jagged data because the problem is that it works by row or by column and if we don't know which column or row is going to have the last bit of data then we've got to go to them all so that makes it a little bit messy so the pros of this method it works on a protected sheet and the cons are it doesn't work well with jagged data and you need to build a range and the code isn't that simple we've looked at formatted so far and none of them are suitable for dealing with jagged data the problem with current region is that it requires adjacent data the problem we have we use range is that it also includes formatting when it's giving us back the range now special cells has the same issue but you also need to save the worksheet before it updates the formatter we looked at range end only works with individual rows or columns so it's not much good when we want to get the last row or column in an entire range so now let's look at a method that does actually work and that is the range find method so this is how we write the code for find so find is part of a range so we're going to use the worksheet and we're going to use cells which basically means the range of all the cells in the worksheet and then we use find now i'm just going to paste in the parameters here because there's quite a lot of them and what you see is that we're looking for asterisks which means in fine terms it's a wild card that means we're looking for any data and we're looking by rows so this will give us back the last row when we run the code you can see the last row in column e9 you can see that that one is highlighted if we want to get the last column we simply change this to by columns now when we run the chord you'll see that h7 has been highlighted if we want to build up the range then we have to get the last row and the last column as we did with previous methods so we declare last row and last column as long integers and then what we do is instead of getting back to range we get back the row of the range and we just do dot row to give us that now let's copy this code and paste it down because it's pretty much the same code for last columns except that we change the search order so we change this one to by rows and then the second one we change the variable to last column and we change the return as column so once we have these now what we can do is build our range so we declare our range here and then we're going to set it and it's going to be a range on the worksheet so because we're using the worksheet multiple times we're going to use the width statement and then it's going to be range and it's going to be the starting cells and the last cell so we use cells for this so we do cells and then cells for the last one and then we'll do end with to to complete our weight statement and we'll do range select now the first cell is going to be a1 so that's row one column one and the last cell is last row last column and that will give us back to range so if we run this code you'll see that it highlights the entire range as we expect it and so you can see at last we found a method that works correctly with our jagged data but now writing all this code every time we want to get the last column the last row or build a range is a bit cumbersome so i've written some code that you can use to make your life much easier let's now look at the four functions i've created for getting the last row one column now we can use find last cell and we simply give it the range and in this case it's the entire worksheet and when we run this code you'll see that it highlighted cell h9 so give us back the last cell now sometimes what you want is to build the entire range so i've created a function for that too and we called this one build range to the last cell so when we run this code what it will do is it will turn us the entire range now we've got two parameters here that are optional and if we don't want to start the range at say a1 we want to start to range at for example row two column four we can set the parameters and when we run the code you can see that it gave us the range starting in the position we specified now one more thing that we want to do is get the actual last row in the last column because sometimes we might want to start with our new data in the row after the last row or in the column after the last column and how we do this is very simple as well we just use the function find last row and the other function find last column and again we just pass it the range where we want to search so let's just print out the results of last row and last column we run the code and you can see the last row is nine and the last column is it you can see that these functions make it very easy to deal with the last row or the last column whatever you want to do it now you can download these functions with the source code from the description below the video if you like this video then please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the bell icon beside it and make sure to check out my other vba videos like the one you can see on the screen here

Channel: Excel Macro Mastery

Views: 103,129

Keywords: VBALastRow

Id: DpwAO5qnvAQ

Length: 15min 41sec (941 seconds)

Published: Fri Nov 19 2021