#Excel VBA to Select the Next Empty Cell or Row - Macros

here I'll show you how to find the next empty cell or row in Excel using VBA and macros I'll show you how to input data into the cell as well as selecting it and two different ways to select empty cells the first empty cell in a column so that would be this cell or the last one at the very end which would be this one before we start check the video description and click the link to teach Excel so you can download the files for the tutorial and follow along and make sure to subscribe and accept notifications so you can see all the new tutorials first up I'm going to show you how the macros work and then I'll show you a few variations of the macros so you can see different ways to do it and that might help you better integrate the code with what you're trying to do in Excel as always download the workbook so you can just copy paste the code that we're gonna talk about in a moment first let's do the macro show you how to use them all f8 and you can see we have a few macros here let's go with the very basic one selects select the next empty row and hit run and you can see it selects this cell right here let's go ahead and do the last empty row this one right here now I'll show you what it's like if you select the entire row we have to go to the VBA window to change the macro just a little bit for that I included them in the same macro so I'm gonna uncomment this and comment this for the Select next empty row okay select Nik next empty row and if you select the entire row goes just like that as if you had clicked this right here and now that you see how they work let us go ahead and go through the code so alt f11 I will put it back to how it was and there's also a select entire row section for the select a last empty row macro right here uncommented but now let's go through the macros okay so what I'm gonna do you saw that I had four macros right here select next empty row select last empty row and then some variations on that input the value into the next empty row and this will work for both of the previous examples selecting the next and last empty row and then how to store the reference of the next empty row so this last one will be just showing you how to better structure this type of macro so that we can get a reference to that cell or row and then use it later within our code and if you download this workbook actually just go down here and double-click module 1 and you're going to get all this stuff right here but for now I'm going to close this so we have some more space and let's talk about the first macro so you see it is just one line of code to select the next empty row and it may seem confusing but let's go through it now and I think you'll understand it a little bit better so you want to start out by telling the macro telling Excel which cell you would like to start with so this determines what column we use so if you use column a you're going to put a 1 here column B but Kalla or put b 1 c c 1 d d 1 etc and whatever row you use in this case it's row 1 that the row is going to start at in order to perform sort of the calculation that gets the next empty row or cell so it seems kind of confusing but let's move to the next part and actually make it a little bit easier to understand so then we have dot and Excel down now what's the easiest way to explain this well let me go to the worksheet really quick remember range a1 alright so let's go back here now when you put range a1 it is as if you have selected cell a1 and the dot end part is essentially hitting control arrow key down so really that is all that range a 1 and dot and Excel down are doing so think of dot end as hitting the control key and Excel down is using the down arrow key on the keyboard there's also excel up left and right or to left - right so you can do it with every arrow key left/right up/down now you'll notice that once you do this select a one and hit ctrl down you get cell a3 but you don't want cell a3 you want the empty one so we have to take this reference and push it down one so we get to offset and if you'll see offset one here that pushes it down one row if I delete this and put the parenthesis back you'll see it has two arguments both optional offset it by the row or the column we want a row offset how much just one so we put one so that bumps the reference down one so now we've started here control down we've offset it by one and we are here but now we have to decide what to do once we are here do you want to select it you want to input something into it you wanna select the entire row what do you want to do well for the simple example we are just going to select it and so the result of all of that is what I showed you a moment ago select the next empty row alt f8 next empty row just like that so range where you want to start dot and Excel down where you want to go using a control and an arrow key essentially offset bump the guy down by one select what you want to do with that guy now that you're there here we select it now if you want to adapt that to select the entire row we simply add one more thing you can see everything here is the same even the dot select except for entire row and if we go ahead and delete this and type period you can start typing you can see you can also select entire column if you want but that's not what we want here select entire row well actually just entire row and then you do something with it in this case we're going to select it now I'm going to comment this out so it doesn't run but leave it in there as an example so uncomment it if you want it to run and then just comment this line out now the very next one selected last empty row it's very similar but there's a few things that have to be changed so we have the big main parts that are just the same right you tell it where you want to start you tell it what you want to do with the ctrl key then you offset it and then you figure out what to do with the empty cell but this one select last empty row so remember that's the one that will select this cell right here we have to in order to get the last empty row in the range and skip all these dudes in the middle any empty guys in the middle we have to start from the bottom and come up so it is essentially as if we start down here in this cell and then we do control up we get this cell offset it down one we get the cell so that's all we're doing in order to do that we have to use this sort of convention or there's a couple different ways to do it actually but this one's kind of easy follows this one right here where you go range a1 well here you do range a and then you have to say what the number if the last cell is since there are older versions of Excel that have fewer rows well you just use rows count that counts how many rows are in the spreadsheet that will give you the last row so a million whatever and then using the ampersand here just combines the a with the million or whatever the number of the last row so this effectively ends up being what is it actually 1,048,576 so that's what this is a 1 million blah blah blah blah so seems confusing but it's not just rows dot count gets the last row combines it was arranged a ok that's where we start now we do dot end hold down the control key same thing but this time instead of going down we go up we use the up arrowkey Excel up just like last time we have to offset it by one row and we do select selecting the entire row same deal except for you have entire row down here so it's easy peasy no problem now let's say that you want to put a value into the cell so let's go all right alt f8 input a value next empty row run high that should probably be a last empty row let's change that believe it's the same one as this one yeah last empty row and so all that we did here that's a little bit different is change the Select to dot value everything else is the same we go for the last row dot index L up offset one but then instead of selecting the cell we put something in it we change the value of that cell you do that by changing the value property so dot value instead of dot select once you've done that you just do an equal sign and whatever text you want or whatever you want in there really so you can see that's pretty easy to do but now what I want to do I want to end the tutorial showing you a better way to well get the reference of the empty cell so here we just did it right here one line okay that's easy peasy but once you have a bigger macro you're not just going to want to have one line like this in the middle of your macro you want to have a little section at the top of your macro probably or wherever it's needed that gets the reference to that empty cell and then you can use it later so what we do for that is we create a variable I'm gonna go ahead and add a comment we're able to hold the range reference okey-dokey so you just do dim give it a name I've named it next empty cell and here you want to set it as a range range can be one cell can be multiple cells now here we have the same code as before and actually I could take off the select select is not required so we choose the cell to start at the direction of the arrow key with the control so control up and off set it by one to get the empty cell but now we want to put this reference inside of a variable so we have the variable here we set it equal to the reference but remember since we are setting an object we are putting an object in here instead of a value instead of a number instead of text we must use the set keyword because we are going to set this variable equal to this object basically so hopefully that will help you remember that you need to put set right here now once you've done that you can do whatever you want with it we're down here you can change the value of it just by referencing the variable so blahblah value equals hi again or you'll see once we just start typing next empty cell dot you have all the options that you would need so select or whatever you want or dot entire row dot select whatever you want so maybe I will leave this in here input text select the entire row and I'm going to comment this guy out and now let's see how it works alt f11 o f8 store reference should be last empty row again whatever run hi again so yes it's a little bit more complicated to do this but actually me before I finish do that so it's a little more complicated to do it this way but the beauty of it is that between here and here you could have tons of code whatever you want to do and then when you want to finally use that empty cell or empty row to do whatever just ref to this variable that way you don't have to have this code just randomly put inside the macro it can make maintaining it much more difficult so just have a nice neat little section here put it in a variable and use that beautiful simply named variable later in your macro wherever you need it and that's how you can find the next four last empty row or cell and put a value in it select it or do whatever you want with it using Excel of VBA and macros I hope you liked the tutorial if it was helpful don't forget to give it a thumbs up and make sure to subscribe and accept notifications so you can see all the new tutorials

Channel: TeachExcel

Views: 31,351

Rating: 4.9140811 out of 5

Keywords: excel vba, excel macro, excel how to, select last row excel, select last row vba, teach excel, ms office, microsoft excel help, vba macros excel, how to make macros excel, programming in excel, programming how to excel, programming vba, programming macros excel

Id: tBXW456R6_E

Length: 14min 4sec (844 seconds)

Published: Tue Mar 03 2020