Selecting Cells in VBA - Sheets, Ranges, ActiveCell, SpecialCells, End, and Offset - Code Included

how this is Chris Gerald everyday BBA in this video we're going to be going over a selection specifically we're going to be going over cell selection but we're gonna start at the top rushing it started worksheet then go down to range and then go down to cells and the reason is is because cells belongs to each one of those so cells belong to worksheets cells belong to ranges ranges belong to worksheets etc and so the more the better that you understand that the easier this is going to make sense over the long term so it may take a little bit more time to just kind of go through this but it is gonna be well well worth it so I've got my visual basic editor open you can hit alt f11 that'll take you here you can also go to developer and then vbe we're gonna call this sub select now we can't call it sub select we gotta call it sub select cells I won't like select to select as a chose okay so the first thing I want to do is just go over the easiest way to select a sheet so it's gonna be sheets and then whenever that sheet name is we're gonna go sheet 1 dot so let no so like does exactly what you think it will do it actually selects that sheet so if we run this it's not going to do anything because I'm already selected on that now if I change this to sheet 2 you will notice if I run that real quick I ran that macro that goes to sheet 2 now one of the important things to understand about selection or selecting is that when you hit select if you are referencing a sheet that is not active it will give you an error so for example if I went sheet 2 dot cells 1 1 this is gonna actually give me an error I guess me an error there because I don't I'm not to do anything and I gotta hit select it select correctly this is a lot more difficult than it seems ok alright so we're good so I'm gonna f8 and this is going to give me an air it's gonna say hey range or class failed its runtime 104 what happened well what happened was as you can't select something that's not active so that's kind of a first thing to kind of run into now one of the nice things selection and selecting things is you can see what the code is actually doing that's one of the nice things about VBA is when you're coding you can actually hit select and you're good to go so I'm going to leave that open I'm gonna put g1 and then we're just gonna type in select so now what's a range well arranged the easiest way to think of a range is it's just anything like that that's a range now this this is actually a range as well if I just highlight that's just range t6 and the easiest way to reference these sheets there it's just two or that sheep reference these ranges is just like you would in a formula so we're gonna do a one we're gonna go dot select now this is also a cell which is fine you'll notice it's selected a1 now if you want to select a larger range then you would do it exactly like you would a formula like if you did a some formula you would type a1 and you would sum to let's say b10 you hit select hit f8 again it's gonna select a1 and then it's gonna have that it's going to have that select from a1 to be 10 so the same is true for cells so you can go cells and the cells are a row and a column index so that is actually a one that is equivalent to a one so if I come in through here I'm hitting f8 I'm going through this it's going to select that whole thing now it's gonna select that single cell now I saw that I was going to do within ranges now let's just say we've got a range here and we're gonna do this I'm gonna go see one to d-10 now I can also go if I go select here it's going to select that range but I can also select a subset of that range because that range has cells now obviously it has four come over here it has that cell if we visually just look at it so I'm gonna go range so we're gonna see one d-10 dot cells dot we're gonna go row 2 column 1 which that's gonna give me C 2 when I in select so let's just go through and see if that if it does what is expected so you notice I've given it a range and I've given it a cell reference within that range that's important and it's gonna select that and it does now there's a couple other selection pieces so the another selection is active cell now what can you do with active cell we may want to know the row right so that would be a common common thing to do I'm just gonna hit debug print because what that's gonna do is it's a spell prank right it's can actually print that in my immediate window if you don't have your immediate window just go to view and click immediate window you can hit control G I'm gonna drag this up real quick and I'm gonna say what my active row is now you'll know that that's two that's gonna give me two you could also do the same thing for the column those are pretty common again I'm where in column three and I what I would encourage you to do is on that active cell is just go through here so you can activate it you could add comment if you wanted some of this stuff you don't really use like for example you probably wouldn't use them autofill for a cell but it's still an option auto fit wouldn't make sense borders around perfect if you want to put borders you can also use borders which is borders dot line there's some additional information there you could clear it so you can use any of these properties and methods on this active cell and so if you go through these you'll find ones that you want you can change your font you can Center it you could merge it there's a lot of stuff that you can do with active cell typically active active cell is pretty awesome actually it's good to just know where you're at so let's look at one other thing let's look at special cells so we're gonna go active cell we're gonna go actually we're going to just do cells so we're gonna go cells dot special cells now what are special cells well if you hit parenthesis the one that I'd like to use is really the last cell and the reason I use this is I look for the last cell row or the last cell column because that's gonna tell me what Excel thinks is the last row or last column and I'm actually gonna print this again and do a debug print and I'm gonna put this the row in the column and this will tell me what Excel thinks for this active sheet the sheet that I'm on it's gonna tell me specifically what that last row in that last column is so I'm going to go ahead and copy over this hit F a favor so it's gonna say wait a sec I've got 16 rows which makes sense because I'm right here and I've got three columns which makes sense because I was actually typing and selecting information over here so that's pretty awesome you got special cells the other thing that you can use is you can use cells dot dot end and what that is going to do is it's going to allow you to go a specific direction so that is equivalent to basically control down so for example if we came in here and we went active cell dot cells dot and and we went X Excel up dot select so that is our that's just like hitting control up we're going to do that and it's going to basically go to that up that Excel up or control what control arrow up would do you could also do excel excel down excel right exhale left so that's actually another cool thing the other thing I want to show you just kind of lastly and your options are up-down left-right make sense the last thing I want to just show you is offset now what's offset took me forever when I started using Excel of course when I use Excel I was for books I was back in the day life is so much better now but what is offset well sometimes you don't necessarily want to go to your cell you want to go to the cell to the left or to the right or up up some or down some well that's what that's what offset does so if we came in here and I'm gonna actually just go range actually what we're gonna do is we're gonna have we're gonna go range we're gonna go D 9 and then we select that's gonna select that cell and now all we're gonna do is we're gonna do a little offset on that now this is gonna select that now we wouldn't necessarily have to do this in two lines of code like I'm going to show you how to do it but that's one way to do it so we're going to arrange d9 denied funny and then we're gonna go offset now the way offset works is you can offset by a column or a row so we're gonna set this by one row and by one column okay which means that we're gonna go plus one down plus one over and I'm just gonna show you exactly what that does I need to tell it to select or it's gonna yell at me which it did that's exactly what it did now a couple other things that you can do on these just so that you can see what you can do is you actually can do - numbers so if we do - 1 - 1 that's gonna get us get us over there all right it's gonna go - 1 - 1 which makes sense quick caveat on this if we would - 100 or 1000 you're gonna get an error because it's gonna say wait a sec this doesn't exist I can't it's it's not defined I can't go to a negative negative row or negative column number the same would be true if you went beyond the bounds of the rows and columns so if you put 2 million as an offset for a row you're gonna get an error so that is a quick overview of how to select using BBA want to do one quick kind of reference thing here just kind of a best practice just as we kind of finish up here and that is depending on how strict you want to be depending on how you want to do your code you may want to use the active sheet and one of the nice things about selection is you're able to select the information and you're able to see it but there are times where you want to kind of make that more strict and so for example if you wanted to make this where you had to be on sheet1 and you had to select in a1 you would actually type in this sheets sheet 1 dot cells 1 1 dot select and that would actually give you that that selection now if you were unseat 2 you would also get an error but you may actually want to have it more strict like that because if you're bouncing back and forth between sheets you may actually want to have that in place and so the proper way to do this if you did decide to do this it would be sheets sheet 1 dot elect so you would do it this way and now I basically select that sheet and then it would select a sheet the cell 1 1 on that sheet and that's a perfect perfectly way perfectly good way to do it now just keep in mind every time that you do select a sheet it does so the computer down because it actually has to process that view so just keep that in mind and one last thing before we kind of get off this kind of a bonus an extra tip is really if you already have stuff selected you can actually use selection and so that selection is basically everything that is selected so I'm gonna go selection not row when I put in a debug print and really this is gonna say whatever selected what is that row now if I had multiple things selected it would actually just give me and if I had multiple things selected it would just give me I'm going to just drag this down and all this code is actually going to be available I include my code and all my videos okay we're driving this down and it's going to give me the row and it's going to give me a I'm in row eight so once you have something selected if you want to do something with that selection the way about that the way to basically do that is to reference selection so if we wanted to I've got if we want it to bold whatever was in our selection we would type in selection dot font dot bold equals true I think that's actually right it may actually not even be true I'm just gonna add is correct then that did bold it so that's one way to do it once you have selected you can actually use the selection function to change that number now you can minimize that so for example I wouldn't have to necessarily go selection I could actually just type in whatever range I wanted to do that and so what that would look like in this case is it would be range the eight dot font bold I would do the exact same thing so that's a quick rundown on selection I know that that's a lot but the main thing to remember is that a cell belong can belong to a range and a cell could also belong to a worksheet and a range obviously belongs to a worksheet and that's kind of your parent-child parent-child relationship there hope you enjoyed this if you have not subscribed to everyday BBA please do that if you have any comments or questions or like this video please leave it and you appreciate that and I hope you enjoy

Channel: EverydayVBA

Views: 101,751

Rating: 4.7512274 out of 5

Keywords: vba select cell to end of column, excel vba sheet range, excel vba sheets range select, macro select cell a1, excel vba sheets range cells, macro select active sheet, selecting a range of cells in active sheet vba, excel vba specialcells, excel macro select offset, activecell offset range, active cell excel offset, vba select cell range, excel vba range offset, everydayvba, active cell offset visual basic, vba select sheet, excel vba, microsoft excel (software)

Id: 6qoQv5ws_SM

Length: 14min 51sec (891 seconds)

Published: Tue Nov 01 2016