Excel VBA Basics #8 - Find the LAST ROW or COLUMN dynamically and clearing out your last report

welcome back youtubers this is Daniel strong with another exciting time with excel vba is fun here in lesson a to the beginner series we are going to talk a little bit about the the end method using excel up excel down excel to left and to right let me show you what that means if you've been watching up with excel is fun with Mike Garvin's videos you'll know that if you say click down here and you want to go to the nearest not filled range you need to hit control up and that takes us here and if I were to hit control right it would take me to the end of this string of not empty cells so control right if I hit control up it'll take me to the top control left so that's using the end feature if we were if we were to record a macro right now in fact we can do that let's hit record macro and I'm going to hit control up it took me from here to the next non-adjacent cell or I guess you could call that the next not empty cell now stop recording we've only done one command control up let's go to module two and see what it said selection not end using Excel up and then select it we're not really going to use that today we're going to use a kind of a custom one because whenever you're building a report that you want to generate over and over every time you click a button every time you do something you want it to go through generally and select whatever the specific range like to a2 through c10 and delete it and then run a new report that might have a different number it might go to 18 or might go to 400 so here's how we find that out using the end feature we're going to say this workbook dot sheets my report okay dot and we're going to use the cells again but here's what we're going to do we're going to say rose dot count comma 1 now I will explain thus far for the row you get row and you always have column after the comma for the row taking the number which is the number of the count of how many rows in this workbook the comma one so we're going to get we're talking about column a okay it then makes sense yet I know dot end so we want to go to the end and then we're going to use Excel up okay and dot row it probably makes no sense whatsoever what we're telling it is we want to know the row number of starting at the cell that's right here which is the 1 million something with row comma column one we started that cell and we want to go to the end going upwards not down left or right and we want to know the row number that should get us right here should stop right here they won't even select it it'll just tell us the number is to the row number is ten because we want to know the row number ultimately so let's say that last row equals we just gave it a name it's random you could say anything you fill in anything right here last row equals block let's see what it does I'm going to debug by hitting f8f8f8 nothing there last row as a variable that's empty but when I hit f8 it sure enough it picked up ten what if I put something right here in row 13 let's see what happened so I'll just go back let's backtrack I'm going to take my little arrow and backtrack a little right now last row is equal to 10 but if a to f8 it should stop right here because this will be the new end of it okay last row equals 13 now let's do something that is very important when you're generating ports that you want them to clear out before you rerun it so we're going to say I'm going to copy and paste this workbook that sheets this workbook that sheets my report copy that let's say dot range we want to use the range of a 2 through so a 2 through C and I'm going to put in quote there I'm going to put an ampersand to join a 2 through see something what row we're going to use let's see how about last row whatever last row is and it will say dot clear contents the clear contents will is just like selecting everything from a 2 through C last row that would be c-13 a 2 through c-13 and just like selecting and hitting delete only you don't have to select it let's backtrack okay last row is 13 and we're going to take the range of a 2 through C 13 and clear the contents and if I hit f8 sure enough it cleared the last report everything from here just cleared it I don't know it doesn't have to be selected then if you run the macro might hit F actually before I hit f5 I'm going to take out this thing that makes it go invisible because that's not going to help us today f5 ok from 1 from 2 to 10 it went ahead and shoved it in there so now if we hit f5 you don't see anything but immediately each time it is actually selecting finding the last row they can a 2 through C and last row C 10 and clear in the contents and then re running the report really quickly so when you hit f5 it just lickety-split sit anyway that's how to use the in dot end a feature and oh sorry about that and we're back let's see the next thing I wanted to show you know the excel up but you can also use Excel I believe it's two left excel two left now right now that wouldn't do us any good because currently it's going to the very bottom most row and column one it's going control up so if we went to the very bottom row and we had control left where does it go nowhere it's stuck right here if we did set it to control right or Excel to right it would take us all the way over here and generally we don't want to be here the very last cell on the whole workbook so let's go back up here but let's say for example we did the selection selection it depends on where you click let's see it mice current selection right now is on g6 and if we did Excel to left from selection let's try that instead of this workbook done sheets excuse me instead of this work worksheets my report dot cells blah blah blah let's just do selection let's change the whole pace selection dot end Excel to left dot row okay let's try that and we'll call this last column last call and again this could be anything you want let's try that last call so I'm gonna hit f8 to debug last column is selection going all the way to the end going to the left it should say ooh let's instead of that row let's do dot dot column okay that way we will get the column number which should be one two three let's see what happens last column equals 3 what do you know it's just like is if you did this and with which so I'm going to randomly put some stuff in here in different columns okay you're seeing that I'm just putting random letters here so let's have fun with this let say wherever I select my cursor ad I'm going to take the selection it's going to take the selection and go all the way to the left to the end and give me the column number so in this case you should go stop here at I which I don't even know that would be number 9 I guess let's see you last call is 9 here I'm going to stop it let's click here F 8 f8 is 9 yep 9 let's click here ok I selected that and let's see well backtrack up here last column is ok that's also on 9 let me erase the 1 and I and let me just rerun that on last column is 6 which is let's see here right here on F so sure enough anyway that's the excel you got excel - left excel to write or excel up or excel down and it's just like holding ctrl and hitting one of the arrows control up control the left control right control up control down so anyway thank you for watching

Channel: ExcelVbaIsFun

Views: 222,263

Rating: 4.8397017 out of 5

Keywords: last row, vba last row with data, vba lastr row, vba loop until lastr row, excelvbaisfun, excel vba is fun, vba basics, vba is fun, excel vba, vba excel, excel vba tutorial, vba tutorial, microsoft excel, excel vba programming, vba excel programming tutorial, excel userform data entry, vba excel tutorial, एक्सेल vba, vba एक्सेल, تتفوق vba, فبا اكسل

Id: K0VqyXLJBOw

Length: 10min 17sec (617 seconds)

Published: Sat Feb 23 2013