Excel VBA Introduction Part 45 - Finding the Last Used Row, Column and Cell

welcome to this Wisel tutorial in this video we're going to talk about how to use Excel VBA to find the last used row column and cell in a worksheet I've made this video as kind of an answer to a question that crops up a lot on some of our previous videos where we're trying to find the last cell the next available blank row in a worksheet so I'm going to start with a quick little recap of using the end and offset properties to do exactly that and then explain why those just sometimes look a bit limited so I'm going to talk about using the end property to end downwards and upwards and in doing so we're going to talk about how you can count rows in a worksheet as well to make sure your for your code will work for legacy workbooks as well we're going to talk about the effect of blank cells on the UM property on Y that can stop the M property from working as we intend it to and talk about a quick way to fill in the blank cells and then we're going to talk about a couple of them more exotic ways to achieve the same sort of end results so can look at the use range of a worksheet and how that's affected by by populated cells and non populated cells and then we'll talk about using the last cell properties from the special cells you can select in a worksheet and how that's related to a blank cell as well the last part of the video we're going to talk about using the find method which something we've covered in previous videos as well but we need to find method to find the last populated cell in either a row or a column in the entire worksheet so it's quite a lot to do quite a few useful and interesting techniques here let's get started I'm going to start by looking at one of the most common techniques I've used in this video series to do with one we've got a list of data and we're trying to build the list we want to find the next available blank row at the bottom now this works beautifully as long as we've got at least two rows populated with value so here I've got a simple worksheet with a header row and some sample data not particularly interesting data I have to admit but nonetheless it gives us enough information to work with to find the next available blank row at the bottom so this is essentially a three step process step one is to find the top cell in list which is usually a known starting point so in our case we know that it's cell b2 that's got our headers had a value in it then from now on to get to the end of the existing list so assuming there are no gaps in this I'm essentially going to replicate holding down the ctrl key and pressing the down cursor key on my keyboard which takes me to the last available or last populated cell in the list and then from there I want to move down one further cell to leave me in the next available blank one so to achieve all that in VBA let's head into the visual basic editor versatile and let's insert a new module and then let's give ourselves a quick new subroutine called something like find next blank row so to break this down into three separate instructions we said that we needed to find or select the top cell in our list which is range b2 and then from there we can go to the end of the list in a downwards direction so we can say active cell dot end Excel down dot select and then from there we can move down one further cell and say active cell dot offset one comma zero not select and just to demonstrate that that does exactly what once it's do I'm going to store down the VBA editor window and then step through this sequence using the f8 key so I use f8 step through range b2 dot select is exactly well get selected then find the end of the existing list so I'm going up in b3 in this case and then offset one further cell down that leaves me in the neck blank row now what I've done commonly in the videos that uses technique is I've combined these three instructions into a single line of code so to determine show you how that works I'd say range b2 dot end excel down dots offset 1 comma 0 dot select and then that's all three instructions essentially wrapped up into a single instruction so it's more efficient both to write and to execute that code so let's just demonstrate that by commenting out the three lines I wrote previously and then let's make sure I've selected a cell that isn't the one I'm about to end up in and then if I step through is not much point stepping through admittedly it's only a single instruction but if I do execute that line you'll see that it immediately takes you to the next available blank cell in the column we're moving down now as I said I've used this technique a lot in this video series to find the next available blank row to build up a list but whenever I've done so I've always made sure that I'm working with a list that has at least one row of data in it along with the header row now often I get asked the question or comments pop up on the video is quite a lot so in this line of code in particular doesn't work and the most common reason for that is because people have started with just a header row and no data in it so let's just show you the effect of that if I take away the row of data or even just in fat just the single value of 1 and column B it doesn't really matter but let's take with the entire row of data to simulate what a real-world situation might be and then I go back to the VB editor and I soon as I attempt to run this subroutine it will fail immediately with a runtime error there's not much point clicking debug because there's only one single line which could have gone wrong at that point is the only line in the entire procedure so what's actually going on there well this is probably easier to describe if I go back to using 3 separate instructions let's just comment out the single instruction and uncomment the 3 that I commented out earlier and if I step through this time if I find range b2 selects that's fairly obvious what that's going to do and then end Excel down now when there's no data below cell b2 or the one that I'm in at the moment the active cell then there's nothing to stop the selection follow all the way down to the very bottom to worksheet 1,048,576 rows down the next instruction says move one cell further down from where I am currently the active cell and of course there aren't any more rows to move down to so that's the point at which we generate the runtime error so what can we do to solve that problem well first of all let's end that procedure let's head back up to cell a1 and then let's go back to the VB editor and think about ways we could get around this issue one simplistic way to get around this problem is to rather than start at the top of the worksheet and go downwards start at the bottom of the worksheet and go up within set so we know that we're in column B and that's where our data our first column of data is going to be then rather than referring to cell b2 we could say arrange B 1,048,576 and then from there we can say end Excel up rather than and Excel down now what that would do is take us up to the the first populated cell moving upwards and it was direction and then from there wherever I end up I want to move one cell further down from there so from that point if I use the f8 key to step through we'll start at the bottom of the worksheet end exhale up and then offset one cell further down so even if there's no data in the list then it takes me to the next available blank row just to prove that it does indeed work when there is data in the list let's just fill in some quick boring data again and if we go back to the VB editor and step through that so Britt seen again down to the bottom of the sheet end Excel up oneself at the down I'm in the next available blank row and of course again you can combine those three instructions into a single one so if I just copy the cell reference rather than type it out skåne and then end Excel up rather than down and then of course uncomment that line of code and comments out the previous three if I just execute the entire subroutine making sure I start in a different cell just so you can see something happening and then run it I'll end up in the next blank row now one time when this can be an issue is if you find yourself still having to sports legacy workbooks in the old XLS format prior to Excel 2007 the maximum number of rows in a worksheet wasn't 1,048,576 it was only 65,536 so if I tried to refer to this cell in a legacy version of Excel then of course that's not going to work now one short simple solution here I suppose is we could say range B 65536 and then end Excel up but in a modern version of Excel if I've already used that number of rows then this technique isn't going to do as any good whatsoever so what can we do to solve that problem one relatively simple solution isn't so worried about specifying the exact number of rows on the worksheet we can actually find that piece of information out by using the property of the worksheet itself so rather than saying this range b1 0 4 8 5 7 6 we could instead use the cells property so we will say cells and then open some parentheses now again we've covered this in a previous video but the cell 40 essentially lets you specify the row number and the column number of cells you want to reference so in this case what I would say is to find the number of the row I would say something like active sheet dot rows dot count and that will calculate in this case in this particular workbook 1,048,576 but in a previous version of Excel in a legacy workbook with the XLS format it would be 65536 the column number that I want to specify here is column number to become another own column B and then if I simply rather than type it all out again just copy and paste the rest of that line of code so I end Excel up and then offset one cell further down and then finally select that cell so again if I comment out that line make sure that I've selected a different cell in the worksheet just to prove that this does indeed work and then execute the inside thing I'll find myself once again in the correct next blank row and that's either with or without rows of data in the list now all of these techniques work fine so far as long as we can guarantee that column B is always fully populated with value so every single row of data has an ID number associated with it if you're in the situation where you've got gaps in a list or potentially have gaps in the list then you think of a different solution so it's just simulate some some data that might have some gaps in its let's have another ID number and we'll say another really interesting bit of data the F 4 5 6 and then the next row we won't bother putting an ID number in and we'll have GHI and it will leave the number blank this time perhaps the next one will have a new value let's our ID number 3 and then while JKL maybe maybe leave the number out again and then perhaps leave MNO and then put a number in this time what we go 4 7 8 9 and then for the last row as you will just leave it at that so get a bunch of gaps in the list some blank cells in both columns B and D now one ballot technique to solve this sort of a problem is to first of all populate all the blank cells in the region of data with deme values it could be a value like minus 1 which you wouldn't ordinarily see appear anywhere in the list or it could be a literal word like literally the word blank or the word null something along those lines so there's a technique in VBA that allows you to to select just the blank cells in a region of data let's have a quick look at how we can do that back in the VP editor let's have a new quick subroutine which will call something like fill in blanks so the starting point for this is referring to the entire region of cells in which our list sets so as a quick simple segment you can use to do that if I refer to the top left-hand cell or indeed any populated cell anywhere in my list but range b2 seems like a sensible obvious choice because we can sort of guarantee that always the start of our list there's a property of cell called current region and now I said current region dot select and then simply execute that so resume you'll find that it selects the entire block of cells is kind of the equivalent of clicking into a cell in a list in Excel and then pressing ctrl a he will show up for select all it selects the complete region of data up until the next completely blank column and completely blank row all the way around the blog now in this case of course it's not the entire reading themselves we want to select it's just the blank cells within it so instead of saying current region dot selects what we can do instead is a current region dot special cells now there's a whole range of different special cells using the special cells method you can select in this case I'm going to refer to if I set it open it's out of parentheses I'm going to look for Excel type excel cell type blanks and if I were to do that and then say dot select and then execute this subroutine again you'll find that this time it selects only the blank cells in that region of cells the final thing we do then rather than just selecting the cells is we'd probably change their values in some way so let's write so rather than select a pin to this statement will say dots value equals I can spell value properly and then we could put in a literal word order or a number let's put some like let's put the literal word blank and this time if I just make sure that I haven't selected any cells in the worksheet and then just just so you can prove this works without selecting things and then run the subroutine again we should find this time that all the cells that were previously blank now have the literal word blank in them so this would solve the problem of not being able to select the next completely blank row if I do indeed go back to the find next blank row routine and then run that one I'll find that it does indeed end up on the next completely blank row of the table but there are legitimate reasons why you might not want to fill in your blanks you might not want to mix and match data types for instance so this sort of catch-all approach where you just blank it fill everything with whether the same word or the same numeric value like minus 1 you might have dates in one column and numbers in another insect in another and you may legitimately not want to mix and match those data types so take things back to the way they were before something I can't undo that because you probably well know you can't undo what a piece of VBA code does so let's just do that manually let's think a couple of different solutions we could use one possibility we have here is to refer to the used range of a worksheet so just to demonstrate what that is let's create a new subroutine calls me like select used range and to do that what you have to do first of all is reference an entire worksheet so I could say active sheet or in this case I'm going to use because it provides me with the intellisense I'm going to use the code name of the worksheet which currently is called sheet 1 you'll always be able to tell the coning worksheet it's the one that's not in parenthesis in the object Explorer the project Explorer the top left-hand corner so I say sheet 1 dot used range not select if I were to do that and then execute the subroutine I'll find that it selects it's actually in this case it's the equivalent of the current region because I've got a single table of data but if I had something like another region of data somewhere further down let's say if I added in a totals or a total cell further into the worksheet and I said in here equals sum and then referred to something like that block of cells so even though there are gaps in this list here if I if I said current region that would be the equivalent of that block of cells if I say use range and execute that then you'll find that it's actually the entire range of view cells on the entire sheet now if I delete that total and that sum function that I've added in there and then just go back and then execute this select use range routine again our final it goes back to selecting just the table of data that's populated so the usual range is just the cells that are populated at the time now this may not seem massively useful just yet to achieve our ultimate aim of selecting the next available blank wrote in our list but we can use the use range to calculate the next available blank cell so to do that we need to rely on the fact that rather than just selecting the entire use range we can refer to individual cells within it so if you say sheet 1 that use range cells this is the same cells property bulleted earlier on where you specify the row number and the column number but in this case is just the row number and the column number within the use range so for instance if I said row let's say row 3 comma column 1 and then close the parentheses instead not select then what this will do is refer to Row 3 cells 1 2 3 column 1 column B in this case in just this block of cells I'll end up with cell b4 selected so if I were to execute that that's indeed the cell that I end up with so having established that what I can now do is replace the number 3 with something that calculates the last row in the use range it's fed stair could do this in a similar way using the current region I can refer to the cells property of the current region but again as I said before the use range refers to the entire use range of the worksheet even though there are gaps in this list we had some more data further down this would refer to the the complete use range rather than current region which is just the single continuous table so let's say rather than the number 3 here I'm going to replace that by saying sheet 1 dot used range dots rows dot count start telling the total number of rows which in this case it would be 1 2 3 4 5 6 and then if I were to run that subroutine I'll end up with the last row the first column of the last row selected then all I would need to do from that point is move one cell further down to end up in the next completely available blank row so let's just insert that before the dots I'll actual say dot offset 1 comma 0 dot select so once again running the entire thing in one go if I execute the inside thing I'll end up in what is truly the next blank row there's another technique that we can use with similar functionality to use range that allows you to find the last used cell on a worksheet so not to demonstrate that let's have another quick subroutine called a fine last use cell and then in here so that might be cell rather than cells I mean here what we're going to do is prefer first of all to any cell in the entire sheet so I'm gonna refer to range a1 and then from there this is related to the special cells method that we learned earlier on if I say special cells and then open some parentheses another one of the special cells we can refer to is the Excel cell type last cell so I say Excel cell type or last cell and then say dot select at the end and if I were to execute that so we seen I'll find that is the last populated cell in the worksheet that's currently selected now there are a couple of slightly more unusual things about the last cell first of all it might not actually refer to a cell that has had a value stored in it so just to demonstrate that let's have a couple of extra rows in the ID column let's have four and five in the list and if I always go back to the VB editor and execute this instruction again we'll find that it's not a cell that's actually got a value in it to this consider the last cell it's one in the last populated column in the last populated row this behavior extends to columns as well so if I were to add in a new column heading in the worksheet let's say unless a date I won't actually bother putting any date values in there but if I were to go back to the VBA editor again and execute this single instruction we'll find that it's a last two populated cell or sorry the last cell in the last populated column and last populous road it's selected one other unusual thing is that the last cell remembers cells that were previously populated but we're subsequently cleared so again just to demonstrate that let's pick a random cells somewhere further down and enter a dummy value into it and then if we were to go back to the VBA editor and execute this single instruction will find that it is of course that cell that gets selected but if I then subsequently clear the contents of that cell and head back to cell a1 and then execute that instruction again we'll find that it's the same cell even though it's now blank it gets selected this is slightly different to the used range we saw that the use range as soon as you clear the content of cells than the usual range sort of collapses and and shrinks back to just the populated cells but last cell doesn't now there are a couple of ways you can clear this this this last cell is sort of this cell is considered dirty at this point I think that's the correct terminology so what we can do to clear that is we can save the workbook or we could close it Excel to close we're down only open and of course but the simplest in theory is just to save the workbook if I hit the Save button either in the VBA editor or in Excel itself then where I to execute that same subroutine one more time we'll find this time that the last use cell is once again the last cell in the last populated column and the last populated row okay just to wrap up the video then let's say you're in a situation where there was a specific cell you wanted to find so unlike the last cell which it could well be a blank cell in a column or a row without using it let's say for example you wanted to find a cell in the last row that was populated like so let's say we want to find cell c9 or indeed let's say we want to find the cell in the last populated column in the actual last populated cell in the last populated column let's put a date in now just put today's date in a cell by pressing ctrl semicolon on the keyboard quickens shortest way to do that so let's we want to find either of those two cells how could we how could we achieve that now this technique actually involves using the find method and I've made a couple of videos on the find and find next methods as part of this series already so I won't go into a huge amount of detail on the find method itself but what I will do is show you how you could use the find method to find let's say first of all I want to find the last populated cell in the last used row of a worksheet so that's having used subroutine to damage like that let's give myself a bit more space and let's think of a nice sensible name sub last used cell in last use row does that make sense well that's what I'm going with anyway I've typed it all out now so to make this work we've got to refer to empty in this case all the cells on the worksheet that's what we're trying to apply the find method to so i'm say cells a dot find and now i'm going to open up a set of parentheses and there's a few different parameters we need to pass bodies into this time first of all the what parameter and glennda namely so that you read this one hopefully a little more clearly and in fact let's break it down across multiple different lines as well so we can read it more clearly so I say find what colon equals and what I'm going to use I'm going to use a wild-card character the asterisk which essentially says find anything any cell content at all so once I've done that I'm going to type in control space and then I'm going to say I'm going to specify the after parameter as well now they have to predominate refer to a range of cells and to essentially where to begin your search after I'm going to begin my search after range a one I'm going to say range a one and then another comma and a space underscore and let's say let's see what else I want to specify this search order this time actually leave the default search order that I'm going to put it in here anyway just so you can see more clearly what we're doing so I'm going to say search order colon equals Excel by rows then another comma space underscore and finally what I'm going to do is specify the search Direction so I'm going to say search Direction colon equals and I'm going to say Excel previous so this is why I specified range a 1 as the after argument so if I say Excel previous excuse me let me just close the parentheses there and finally say dot select at the end so it actually do something useful with this so if I say range a1 and then go to the previous cell that will actually start searching the worksheet backwards so the last cell that it the first cell that it actually searches in is cell xfd 1,048,576 so it's going to start all the way down in the bottom right hand corner of the worksheet and work its way backwards now it's searching by rows so it's going to go go through all the columns in one row before it searches all the columns in electro and so on and so on and so on so essentially the first cell that it's going to encounter searching for a cell with any value in it should be in our case cell c9 so let's just give that a quick try and make sure that that works I've got cell a1 selected and if I just run the civil tune I'll end up with cell c9 selected from that example you can probably work out how easy it is to find the last use cell in the last used column so let's just give this reason a quick copy paste copy the inside thing paste it in a couple of lines further down last use cell in last use column we essentially only need to change one single thing apart from the name of the subroutine of course rather than searching by rows we simply want to search by columns instead so to specify that we say search order Excel by columns and in this case what we should find is that cell e7 becomes selected so once again if I just execute a subroutine we'll find out that it's the last youth cell in the last used column that becomes selected so whew quite a lot to take in there but um hope if that's cleared up a few of the little issues particularly one surrounding searching for the last or the next available blank cell when your list doesn't already have any data in it that's the main reason I made this video to sort of answer that question there's lots of other usual techniques you can take away from this video as well hopefully going to find some of those useful in the real world any questions just post me in the video below in the comments and thanks for watching if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials leaving some exercises that you download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 34,287

Rating: 4.9215684 out of 5

Keywords: vba, excel, last cell, last row, last column, used range, last used cell, wise owl

Id: pI2px2KoapU

Length: 26min 22sec (1582 seconds)

Published: Mon Oct 24 2016