Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)

welcome to this YSL tutorial in this video we're going to teach you one of the most fundamental things need to understand to use Excel VBA and that's how to select some refer to cells we'll start the video with looking at how you refer to cells based on their absolute position on the worksheet so that's using the word range all word cells and using cell references or row and column numbers we'll also show you how you can refer to a cell that has been selected using the active cell keyword and then move on to looking at selecting multiple cells how you refer to a selection of multiple cells and also how you could use a range name to refer to a range of cell you might have previously defined once you've coded absolute positions we're going to talk about how to select cells relatively there's loads of useful things you can do there such as finding the end of a list moving up down left or right on the worksheet a specified number of rows or columns and also things like selecting from the top to the bottom of a list and selecting the entire regions and entire columns so we've got quite a lot to get through we best get started you won't get very far with Excel VBA unless you understand how to select or refer to cells and this video is going to teach you several different ways to do that all of our examples are based on this list of the top ten highest grossing films of 2012 and we're going to be writing subroutines which will add new films to the list so to start with we need to get into the VBA editor and the quickest easiest way to do that is to hold down the Alt key on your keyboard and press f11 when you get into the VBA editor we'll need to insert a new module and we can do that by right clicking in the project Explorer choosing insert and choosing module and then finally we'll just quickly rename the module and I'm going to go mine mod selecting cells so that we're ready to go we'll start with a quick look at how you can select single individual cells based on that absolute position on the worksheet so let's have a quick subletting in which we can write the code that will do that serve select single cells by position enter a couple of times and tap once so what I need to start doing now is selecting single individual cells if I quickly switch back into Excel itself I'll turn Dec 11 on your keyboard the cells I need to select are a 13 B 13 and then C 13 and I need to fill in the cells with the relevant values so to do that I'm going to show you first of all the most common way to select an individual cell that's using a keyword called range range is a type of object in Excel VBA and is how Excel VBA describes any cell or block of cells is referred to as a range object you can use this keyword to refer to a single individual cell by typing in a set of round brackets or parentheses and then double quotes and then the cell reference of the cell that you want to refer to close the double quotes and close around brackets and then what I would like to do to this object is I'd like to select it if I type in a full stop that should display the intellisense list the method that I'm looking for in this list is called select I type in letter s select is the first item that I find so all I need to do now is press ENTER and that's how you select a single cell based on a cell reference now I need to change the value of the cell that I've just selected and when we're talking about individual cells there is a quick and easy way to refer to the individual cell that is currently selected on screen and the keyword that will do that for you is called active cell now just quick reminder if you don't want have to type in long keywords you can always hold down the ctrl key on your keyboard and press the spacebar to attempt to force the intellisense list to appear and that will allow you to pick out certain long words and means you don't have to type them in so the word I want is called active cell and what I'd like to do to the active cell is changes value so type in a full stop after the word active cell and look for the value property and there it is it's the second one after the letter V so what I'd like to do is change the value to be equal to the number 11 which is the next number that I want to add in column a so there we go two lines that will first of all select a single cell and then change the value of the cell that has just been selected the next technique will look up for selecting the single cell allow you to refer to a row number and the column number rather than a cell reference so what we'll do although I could write range B 13 dot select what I'm going to do instead is use the cells keyword cells if you have an a set of parentheses after it allows you to specify a row number and the column number for a cell that you want to refer to so the row number here will be cut will be row number 13 follow that with a comma the column number will be column number 2 which is column B close the parentheses and then what I'd like to do is select that cell if I type in a full stop you can see hopefully one disadvantage of using cells compared to range is that it doesn't display the intellisense and I can't press ctrl + space to force the intellisense list to appear either all you have to be aware of really is that when you use the cells keyword it returns a reference to a range object so the things that you can do to that range object are exactly the same as what you can do had you used the range keyword instead so I can say cells a 13 comma 2 dot select hit enter and then what I'd like to do is change the value of the cell that has just been selected so again if I press ctrl + space at the start of the next line look for the word active cell dots value equals and then apparently the 11th highest grossing film of year 2012 was a film called The Lorax I can spell up properly it doesn't matter a bit of an odd made-up word anyway and any literal text that you want to use in a Cell you must enclose in a set of double quotes so unlike numbers which can be just written directly into your code you must enclose literal text in double quotes so there we go those are two values for our film filled in so there's one final way I wanted to show you for selecting the single cell it's a fairly unusual technique you're unlikely to see this used frequently in the real world but there's a shortcut way to refer to a cell based on its cell reference without having to use the key word range or the key word cells you can actually in a set of square brackets type in the cell reference of the cell you want to refer to and again if you type in a full stop after you've done this you don't see any intellisense but this is just a quick shorthand way to refer to a range object and you can again use exactly the same list of properties and methods had you actually used the word of range so c-13 dot select then hit enter and I want to change the value of the newly active cell so controlling space to bring up the intellisense list look for active cell time 2 . look for the value property and I want to make this equal to a release date now according to Wikipedia of the lorax was released on the 2nd of march 2012 when you want to enter dates in VBA you need to enclose days in a set of hash marks and UK users will need to be very very careful about typing in their dates in there in the UK date format if I typed in to 4/3 for size 2012 all those who were - a person from the UK that looks like the 2nd of March 2012 that's actually February 3rd 2012 so yes unfortunately dates are in u.s. format in VBA regardless of what the language settings of your computer are so there are a couple of different ways around this you could just I suppose remember that it's month first and day second or if you prefer you can type in your dates like so you can type in your days as 2 followed by a short three-letter code for the month followed by the year when you hit enter at the end of the line you will see that this date format changes but what you can guarantee is that the month goes into the correct position and the day goes into the correct position so essentially you don't have to worry about which bit is where so there we go three different ways to refer to select and change the values of cells so all that remains for this symbol routine is to execute the code to make sure the values go in the right place and I think it's probably worthwhile stepping through this procedure to make sure that we can see each line happening as we expect it to so I'm going to resize my screen slightly so that I can see excel in the background okay so I can see the cells aren't going to be a be referring to and then I click anywhere inside the subroutine I want to to execute and then plus the f8 key once so each subsequent time I now press the f8 key you'll execute the line that is currently highlighted so press f8 to reach a range a 13 dot selects when I execute this line we ought to be able to see in the background that cell becomes selected so f8 and there it is so the value then becomes 11 for the active cell and then we move on to the cells 13 comma 2 and also that cell b13 and so on and then c-13 a slightly unusual method which also works and then that changes the value to err to the 2nd of March notice again UK uses that when you transfer the value into a cell in the worksheet which is a date then that takes onboard the date formatting of your computer so so the front end of Excel will use the regional settings of your computer whereas there is just the VBA code itself which doesn't so this is a promise this is the 2nd of March 2012 not February 3rd so I press f8 1 violence I'm just end the subroutine there we go there selecting single cells based on absolute position one thing that's very important to realize about selecting or referring to range objects is that it's implied that you want to do that on whichever worksheet is active when you execute the code so I can demonstrate what I mean by that by manually selecting a different worksheet in my workbook and then return to the VB editor and execute this subroutine again I'm going to do this by using the Run button or pressing f5 on my keyboard this time so it should just happen as quickly as possible and hopefully you can see that this has happened in whichever worksheet I have active so it was in g2 this time that could mean that when you do this sort of thing in the real world you'll want to write a line of code which first of all selects the relevant worksheet so if I add a line to the top of my subroutine which will which will do that for us I'm going to refer to worksheets sheet 1 I'm going to say dot activate now there are many many different ways to select or activate worksheets this is just one simple technique that you'll find commonly use we'll have another video which explains the various different ways of referring to in selecting worksheets later on but this will make sure that when I execute my code whichever watch it does on to begin with I change from that one into what you want and then change the values of the appropriate cells so I use f8 to step through this subroutine now you can hopefully see at the bottom I'm still on sheet 2 if I use f8 to step through this routine when I reach this one worksheet sheet won't activate I first of all move to sheet 1 and then subsequently select and change the values of all of those cells game you can even extend this technique of referring to a specific worksheet first and by referring to a specific workbook as well if I pop back into Excel briefly and create a quick new workbook I can do that by pulling on the ctrl key and pressing n so have a new workbook which is called book 2 as I haven't saved it yet it doesn't have a file name extension if I go back to the VB editor now in my project Explorer I should see a new project listed for that new workbook if I extend my screen down again you can see VBA project book 2 so what I could do in my select single cells by position subroutine is before I refer to a specific worksheet I could also refer to a specific workbook I believe that by saying workbooks open parentheses in double quotes book two which is his name closer double quotes closer parentheses and then say dot activate so it's very similar to how you activate a worksheet one thing to bear in mind is if you have saved the workbook you'll need to provide the filename extension here as well so for example if I was referring to my top movies 2012 I'd have to extend dot XLS M to the name but what I can do now is if I quickly switch back into Excel and I'm going to make sure I'm in the top movies workbook first of all I'm going to make sure I'm on sheet 1 if I pop back into the VBA editor again with Alt + f11 and if I step through the first few lines of this subroutine using the f8 key I'll find that the very first thing that happens in fats you'll see this a bit more clearly if you can see the title bars of the Excel window I'm in top movies 2012 when I hit f8 here it now moves into book two first and then it make sure it's on a specific worksheet and then so on and so on and so on it will fill in the values in the relevant cells so there you go that's how you can get from any workbook into any other open workbook any worksheet in that workbook and then any single cell on that worksheet so far we've approached the problem of changing cell values in the same way you would have to do it in the real world if you were doing it manually we've selected a cell and then change the value of the cell we've just selected but in VBA is possible to change the value of a cell without having to select it first so when I demonstrate that with a quick new subroutine which adds a new film to the next row which we row number 14 so sub change cell values without selecting them I should pick shorter shorter subroutines goodnight train cell values without selecting got that right in a moment there we go so to begin with you need to pick one of the methods you like to use to refer to yourselves you neither use range cells or the shorthand way I'm going to refer to using range mainly because I get the intellisense available with a range keyword again I can use my intellisense at the start of the other line I can press ctrl in space and look for the word range in the list and then open it out of round brackets and double quotes and this time the first seller once referred to is a 14 closer double quotes closer parentheses and then hit the full stop so previously I applied the select method to the range object I've referred to what I'm going to do now is I'm going to just directly refer to the value property so it's Aven letter V you'll see that I can directly refer to the value property of a range whether or not it's selected so range a $14 u equals and then the next number in a list with the number 12 and then hit enter and I can carry on in that same vein I can say range B 14 sorry 14 dot value dots of value try again equals and then the next filmin list according to her to a comedians wreckit-ralph that was the twelfth highest-grossing film of 2012 I've not seen it I don't know if it's any good or not and then finally I can say range C 14 dot value equals and the release date for wreckit-ralph was the second of November so I'm going to type that is to not 2012 close the hash mark and when I hit enter the end of the line I'll know that the number for the month and the number of the day go into the correct place so if I resize the VP editor window so we can see Excel in the background and just scroll down so I can see the subroutine I've just written we can step through this one and show you that you can clearly see that I haven't got any of these cells selected but I use f8 to step through this routine you'll see us by magic almost the valleys appear in the cells without having to have them selected this is a much more efficient technique than selecting a cell and then changing its value you can indirectly change the value of the cell using a technique like this again hopefully you'll notice it's implied the cells you want to change are on the currently active worksheet when you execute the code but you can extend lines like this to refer to cells on a different worksheet entirely so at the start of each of these lines if I refer to let's see worksheets sheet 2 dot range a 14 etc and if I copy and paste that part to the beginning of each of these lines then what this will do is change the value of a specific cell on the specific worksheet without having to have either of those two things selected so if I step through this routine just prove that I'm running it and then what I would have to do then is manually go away in check sheet 2 to ensure those valleys are there and indeed there they are and guess what of course you can extend this yet even further by referring to a specific workbook first so if I create another brand new workbook while I'm here that's going to be book 3 this time back to the VB editor and at the start of each of these lines I could refer to workbooks book 3 dot worksheets g2 and then this specific range object that I want to refer to there again I'll copy this to the to the beginning of each of the other lines back in Excel just quickly make sure that I've gone back to my original top movies 2012 workbook then in the VB editor make sure we can see the screen at the top you won't see this change we're not going to move away from the top movies workbook now but when I step through this routine each of those three lines have been executed on a completely different workbook if I switch into that book three now and head on to sheet 2 I'll see that those relative those specific cells have been filled in from a completely different workbook so you hopefully see that this sort of technique can make your go much more efficient you don't have to perform all these extra selections or activations of workbooks and worksheets and cells you can indirectly modify the values of cells using a line of code such as this one now that we've seen how to select single cells let's move on and look at how you can select multiple cells at the same time so for example we might want to format the titles at the top of our table we'll write a simple routine that will select cells a1 to c1 and maybe change their background color so back to the VB editor first and we'll need another new subroutine I'm going to call it sub selects multiple cells and I'm going to give myself a bit more space below this one just push up the caret so you can see it a little bit more clearly there we go just as with single cells there are several ways to select multiple cells let's start by using range so range open brackets and double quotes the first cell reference I want to refer to as a1 and the end of the block of cells I want to refer to as c1 so if you separate those with a colon like so close the quote in brackets dot select that's how you refer to multiple cells in a very straightforward way if I wanted to do something to the cells that I had just selected I can't use the word active cell to do so active cell only have refers to one cell so when you have multiple cells selected and you want to refer to them you use the word selection instead so selection dot and unfortunately you don't provide you're not provided with any until he sends with the selection keyword that's what I'm going to do is choose to modify the interior property I'm gonna modify the color of the interior again UK users watch out for the American spelling of color there's no u in color in VBA and I'm going to change it to add to a background color I'll display my until he sends lifts with ctrl + space and for one of my RGB colors so I'm going to dark blue I think sounds good there we go so there's one simple way to select multiple cells now just as with single cells is not necessary to select multiple cells before you attempt to change one of their properties so let's imagine I wanted to change the font color of the same block of cells I'll use the range technique again so range a1 to c1 with a colon separating them and rather than selecting the cells I can just refer directly to the property that I want to modify so this time is going to be the font property and hopefully it clear the advantage of this is that you don't lose the intellisense as you do when you use the word selection so font color equals and let's go with RGB white I think RGB white there we go I can also use my shorthand way to refer to multiple cells so if you remember from the previous examples we were looking at putting a cell reference inside a set of square brackets so if I once again to refer to the same block of cells a1 to c1 I can simply do do this you want to see one in the set of square brackets and let's see let's let's change the font size this time so again unfortunately we don't use the intellisense we haven't got access to the intellisense list so you have to know what properties you can change so I'm going to make the font size equal to say 14 and there we go I think that's probably enough formatting for the first row so let's move on to that two row number two which I would just quickly switch back into excel contains the column headings so let's look at a couple of other ways to refer to that block of cells back into the VB editor I'm going to show you a slightly different syntax of the the range technique so if I go for the word range again and open some brackets and to begin with I'm going to refer to cell a2 in its own set of double quotes I don't know if you make out the tooltip that's appearing on-screen here the range property technically has two separate arguments which allow you to refer to effectively the top left-hand corner and the bottom right-hand corner of the range you want to select or refer to so after I've referred to cell a2 I can type in a comma and then in its own set of double quotes again I can type in c2 so effectively refer to the corners of the block of cells that I want to select I'm not actually going to select them all I'm going to do is change the interior color again and I'm going to go for a CR a light blue this time I think do we have light blue there it is perfect so the syntax is slightly more awkward there are a few more characters to type in compared to the original technique we looked at with using range but we are going to see this one use a lot more frequently later on it becomes much more useful when we start looking at relative selection methods so bear this one in mind with knees this one against several more times in this video this syntax would also be the only way we could incorporate cells to refer to multiple cells unfortunately you can't use cells by itself to refer to a block of cells on multiple range objects if we wanted to use the cells technique what we would have to do is enclose it within this syntax of the range properties so what we would have to do is say range open parenthesis and then cells open parenthesis again we just move the mouse cursor the first cell I want to refer to is cell a what is our a - so that's row 2 column 1 close ones other parenthesis then another comma and cells in this case the row number will be 2 again and the column would be 3 so c2 closer parenthesis 4 cells close another set for range and then I can say dot fun starts color perhaps equals I don't know let's give our RGB dark blue it's very incredibly awkward to do I think it's fairly unusual that you would want to use cells to refer to multiple range objects so you'd almost exclusively use range when you want to refer to multiple cells so all we should need to do now is give this quick routine a test to make sure that it does the drop of this meant to do let me - like the screen down so we can see the cells we're going to be affecting and then we'll step through the sponger so you can see that all the things we expect a band actively are happening so range a want to see once select so press f8 and there we go we see those cell selected and then that's going to change the interior color of the selected cells and that works the next line should work whether or not we have those cells selected in the first place let me just quickly click on a different cell altogether switch back to the VB editor and press f8 again and we'll see the font change to white regards of whether it was selected or not and FAS again just to prove that the the other method works that increases our font size and then range a 2 to c2 we're going to modify the ya the interior color of those and then finally we going to change the font color of there of those cells to dark blue f8 one more time on and serve to make sure that we are that we finish running our subroutine and are all the techniques most of the basic techniques are least for selecting multiple cells if you've been using Excel for a while you might have encountered a feature called range names a range name is simply a label that refers to a single cell or a block of cells so it's like a plain English word that refers to cells so we can create range names in several different ways I don't want to spend too long doing this what I want to focus more on how you do this in VBA but let's for instance create a range name which refers to our our list of column headings once you've selected some cells you can click into this area of the screen the name box and if you click into this you can type in a new name for that block of cells make sure you don't use spaces when you type in the name you want to use and make sure you press Enter when you finish typing so what that means is if I click away from the block of cells the name box itself now contains a unique word column headings and if I select it it will take me to that range of cells another quick way to select or create range names is to select a block of cells including some column headings which we want to use as labels so I play series create three range names one called ID one called title and one called release date so once I've selected all of those cells I can head to the formulas tab in the ribbon and look for the option called create from selection this is in the defined names group if you let them ask us a link you'll see there's also a keyboard shortcut for this ctrl + Shift + f3 but whichever the - - opportunity to choose either the keyboard shortcut or simply clicking the tool you'll be presented with this little dialog box now make sure you only have one of these checkboxes checked for this if you're doing this particular example I don't want to use names in the right-hand column what I want to do is use the labels in the top row of my selected cells to create three range names so when I click OK all I need to do now is look back in the name box and I should find that I now have three new range names notice that any spaces that were in the column headings have been place with an underscore character if I select each one of these it shows me which cells those range names refer to should you ever need to delete a range name and again this is the last thing I'll mention I think on range names in Excel itself should you want to delete a range name you can use the name manager so you click on the name manager tool select the region that you want to remove and then simply click the delete button I'm not going to leave mine now because of what I wanted to show you how you can refer to these range names in VBA so to refer to these range names in VBA let's head back to the VB editor and I think we'll have a new subletting for this serb refer to arrange names and we're going to use the range technique once again to begin with selects prefer to our range over of film IDs so we have a range name called ID all we need to do is replace in the range technique we replace the cell reference with the range name itself so range ID dot select or in fact let's just change some sort of fonts probably there's a font dots italic equals true so that will refer to the ID range you can also use the shorthand technique to refer to a range name so if I open a set of square brackets and the next column was title close the square brackets and then I can modify a property of of those cells so let's say font color equals RGB dark blue perhaps and there we go so two techniques for referring to range names so let's give this ability in a quick test to check that it works oh I just resize the screen so we can see the columns will modify and I'll use f8 to step through the routine so we should see the add the ID numbers change to italic and there they go and then we should see that the font of the titles changes to dark blue and then that goes f8 one more time to end the subroutine and that's how you use VBA to refer to range names for all of the techniques we've used so far for referring to cells we've needed to know exactly which cell on the spreadsheet to refer to three other needs now its cell reference or its range name or its role in column numbers what we're going to do now is show you how you can refer to cells relatively based on their position relative to other cells so we'll write a routine which we'll add in a new film to which ever row is the next blank one in our list the methods will use will work basically like this we'll start by selecting cell a1 or referring to cell a1 as you know that's a known fixed starting point from there we'll simulate getting down to the end of the list in a downward direction you do this by holding down the control key on your keyboard and pressing the down arrow key that will jump down to the last populated cell in a list assuming there are no blanks anywhere in that column once we've done that we'll need to move one cell further down from there and then we can populate that ourselves with the next number in the sequence and then we're going to populate the cell one column to the right with a name of the film and the cell two columns to the right with the release date of that film so we need to do is work out how to make that work in VBA terms so let's start by going back to the VBA editor and I think we'll have a new module for this as well so I'm going to right click in the project Explorer choose insert module and I'm going to rename it using the properties window I'm going mod relative select and a new subroutine in here which we'll call add film to end of list and now we're ready to go now I want to make sure that we're on the correct worksheet when we run this subroutine so the first line of code is actually going to select or activate sheet 1 to make sure we're in the correct correct place so worksheet sheet 1 dots activate then we know that our starting point is range a1 so let's we might as well select that one as well and we know how to do that already so I'm gonna use my range techniques of range a 1 dot select now from here what we need to do is move down versatile to the end of the list in a downwards direction so from the cell that I've just referred to or select it so I can now refer to it as active cell I want to use the property called end now end allows you to move in one of four different directions and to specify the direction you want to to move you need to open a set of parentheses and then pick an item from the list they're all fairly clearly labeled so it's unfortunately the one that we want is hidden beneath the tooltip but if I use thick the cursor keys to start scrolling through the list I'll find the woman I want appears Excel down so Excel down close the parentheses and finally I need to say I need to do something to the cell that I've just referred to so I only need to modify one of its properties or what I'm going to do is select it so at this point I'd like to quickly demonstrate what that does so if I just resize the screen a little bit so we can see what's going to happen I'm going to use f8 to step through so worksheet sheet 1 activate we won't see anything happen here because we're already on that worksheet range a1 dot select we already know what that will do this is the important line here what we can see happen is that the next cell will be will be selected is whichever the last cell in column a is so the last populated cell in that continuous column of values so the next step is to get from that last populated cell to the cell that is one below it so to do that let me just resize my screen again to do that from my new active cell I'm going to say active cell dot offset so another property which allows you to refer relatively to a cell now the offset property has two parameters is the number of rows that you want to move up or down followed by the number of columns that you want to move left or right so to move downwards my row offset is one positive numbers move you down the worksheet negative numbers will move you back up follow that with a comma and then the number of columns that I want to move I don't actually want to move any columns left alright so I'm going to provide a zero for this parameter closer parenthesis and once again just like with the end property I need say what I want to do to the cell I've just referred to so dot select once more and again finally what I'm going to do is quickly demonstrate what that what that does if I step through this one reasonably quickly so start the worksheet in cell a1 then end Excel down take it to the end of the list offset one move me down one further cell now I've written these instructions out separately to make it easy and clear to see what each one does but we're doing this in the real world I'd be tempted to combine these three instructions in one go so referring to range a1 then the cell be endless and then one cell further down we can actually that in one single lines let's start by referring to range a1 then rather than selecting that cell I want to refer to his end property and I want to specify the excel down direction for the end property following math I don't want to select the cell at the bottom of the list I want to then refer to the range that is one cell further down from the range referred to by the end property so I can tag on an offset to the end of this open set of parentheses and I want to refer to the cell as one row down and zero columns across and finally at that point I think that I do want to select that cell so I'll take a dot selects on to the end of the instruction so essentially that line combines the three things we're doing in the previous three lines but it does it in one single action there's only one single select method used here which is way more efficient when you come to run code like this it's always the preferred approach I think that you should be aiming for so I've deleted the previous three lines that line now will take us down to our next available blank cell at the end of the list and although it's not going to be very very impressive when I quickly execute it because we'll just end up in at the bottom of the list in one go just to prove that it does work that will take us to where we need to be now that we know we can get to the correct cell we need to start populating there the new blank row with the new values so let's start by adding in the new ID number so whichever cell I've just selected I'm going to refer to as an active cell I want to change the value of that cell to be equal to something now I'm not necessarily going to know what the previous ID number is so what I'm going to do first is read the value of the cell that is one above the active cell so active cell dots offset minus one comma zero dots value all only three then is add one to that value and I'll have added in the new ID number simple as that now to fill in the name and the release date of the next film I need to refer to the cells that are one column across and two columns across respectively so again the next I'm going to start with the word active cell and I'm going to reduce the offset property again I want to go zero rows or pulldown this time but I want to refer to the cell that is one column to the right so as a positive number you use negative numbers to move left by closer parenthesis I don't want to select that cell I'm going to stay exactly where I am in column a what I'm going to do is change the value of that cell to be equal to the next film in the list apparently according to my information that was the film Lincoln which again I've not seen that one either so that will change the value of that cell to be in that piece of text and while we're here I'm actually going to quickly cheat copy and paste this part because I want to refer to the cell now that is two columns to the right so remember I'm still in column a so to refer to column C only to refer to the cell those two columns to the right and the date or the release date of Lincoln was nine not 2012 so there we go that's how you refer to cells based on relative positions so you use end to jump to the top bottom left or right of a continuous block and use offset to refer to cells that are a specific number of rows or columns away from the one you have selected so all that we have to do now is run the subroutine to make sure that it works so let's resize the screen again so we can see clearly what's going to happen in the background and use f8 to step through the routine we'll start with selecting the next blank cell which we knew that one worked already and then the next number should be 13 which is 1 more than 12 and Lincoln should go into column B and that release 8 should go into column C just like so f8 one more time to make sure the subroutine has ended so it would be nice I guess if the film name and film release date were variable as well rather than adding in the same film every single time we might want to prompt the user for the film name and release date but we'd save how to do that for another video one small problem with the formatting of our new film is that it doesn't match the formatting of their films above it in the list so we could fairly easily actually just add on the code that would change the font to italic and the font color of the title to blue but what I'd like to do is show you how you can write code which applies formatting or these refers to cells in a continuous list regardless of how long that list is so it's going to simulate essentially the keyboard shortcut whereby if you have for instance cell a3 selected you could hold down ctrl + shift on your keyboard and tap the down arrow key and that would extend your selection all the way down to the end of the list regardless of how long it is so that's what we're going to do but in VBA so to do that we'll head back to the VB editor and we'll start a new subroutine which we'll call select let's call it variable column or call fort so when we're using this technique we're going to use one of the syntaxes of the range technique so we're going to start by reverting to range a3 we know that that's the first cell which contains an ID number what I want to do then is refer to the cell that is at the end of that list now if I knew its exact cell reference I could simply say whatever it was a 13 or so but I don't know it cell reference because that list could be any height at all so rather than using the cell reference for the cell to parameter what I'm going to do instead is say range open parentheses and then essentially what we did here from range a1 dot end Excel down and we saw previously that this refer to the cell at the end of the continuous list so if I replicate that here with a1 dot m Excel down parenthesis rather than number nine that would help that entire set of code there refers to the cell that is at the end of the list but regardless of which one it is so all I need to do now is close the extra set of parentheses for the range property and then say dot select if I wanted to select it so just to prove that this does actually work if I just resize the screen and make sure that I don't have that block of cell selected first and then quickly use the f8 key to step through this routine whenever that line is executed it looks for the top cell as a 3 and the bottom cell as which ever cell is at the end of the list from cell a1 sonic f8 this line that's the block of cells that I get selected and it doesn't matter how many cells were in this list I could add in any number of extra items at the end manually and if I go back to the VB editor and step through this code again I'll find that that now selects the entire list again so what we need to do next I suppose is if I get rid of these extra valleys that I typed in what we need to do next is then apply formatting to that entire block of cells that would be selected so we can do that with the selection keyword so we'd say selection dot font dot italic equals true and of course just as with all of our other examples we don't necessarily have to select this block of cells before we can format it rather than dot select we could simply say dots font italic equals true at the end of this line finally if we execute this entire subroutine in one go just use the F the f5 key or click the green triangle on the toolbar we ought to find that entire block of cells get selected and the font is changed to italic now I can use almost exactly the same technique to to color in the font solve column B as well so what I would want to do is just quickly switching back into excel is change everything from cell b3 down to the end of the list now the technique I use for column a was to go from cell a3 and then the second seller referred to as a1 dot end Excel down so essentially what I did was from cell a wanna it was like building on the ctrl key and pressing the down arrow key and that works because column a has a continuous list of values from cell a1 all the way down to the end of the list but column B however the very first cell if I select cell b1 there is no value in that cell so you can see that there the title is actually contained all in cell a1 now if you have a blank cell it's easy to Democrat I think in cell with with cell c1 if you have a blank cell and you hold down the control key and press the down arrow key that simply takes you to the next populated cell so what we're going to do for formatting the font of column B is we're going to go from cell b3 and then the second cell will refer to is B to end Excel down and that will take us to the last populated cell in list there's one thing that's really worthwhile pointing out this tech this technique with it with using the end property when you have blank cells what the end property does is not necessarily get you to where you want to be so always check that before you start writing your code but now that we know about that so let's go back to the VB editor and I can pretty much just copy and paste most of this line already so I copy and paste that line we say the screens we're gonna enough space to write the rest of it out I can go from cell b3 I know that's the top cell in my list and then from b2 and exhale down and I'm rather than select those cells I'm going to change the font color to be equal to RGB dark blue perfect so if I execute that code now what we ought to see I'll just run through one one go I should see that the entirety of column B now gets his font change to dark blue as one last example using this technique what if I wanted to refer to all of my data cells to perhaps maybe change the background color of them all I can simulate that in in Excel using keyboard shortcuts so if I have cell a3 selected I could hold down ctrl shift and tap the down arrow key and then ctrl shift and tap the right arrow key and that extends my selection from the top left hand corner to the bottom right hand corner and I can do exactly that in VBA as well if I go back to the VB editor and let's write this is a single line which will select that block of cells so from range a3 that's the top left hand corner the bottom right hand corner I can get to by saying range and then a1 dot end Excel down first of all and I can simply then tag on another end property to the end of this so dot end Excel to write close two sets of parentheses and then say dot select and again I could just change one of the properties of that block of cells but it's easier to demonstrate if I show you that it that it gets selected as well and then I could maybe say selection sorry selection dots interior dots color equals and I need to pick one of my RGB colors let's say I don't know what Alice blue looks like let's find out what Alice blue looks like so those two actual lines now if I resize my screen a little bit make sure that I don't have those cells selected in the first place and then step through this routine when I reach this line what I should see is that my entire block of data cells get selected and that's exactly what it what happens so if I use every now we'll finally find out what Alice blue looks like um it looks like an even lighter shade of blue than light blue so it says you can just I can just make us at least on my screen that I that is a very very very light shade of blue so that's all the techniques I think for selecting columns or blocks of cells relatively so we've just looked at how you can select a block of cells from an own starting point to the end of a list regardless of how long it is or how wide it is but what if you just wanted to select an entire continuous block of data from any cell within it there's a keyboard shortcut in Excel that lets you do that select any cell within a continuous range and press ctrl + a and it selects the entire block unfortunately there's a way to refer to that in VBA as well so what we'll do is we'll write a quick routine that copies this entire region of data onto another worksheet so let's start by going back to the VBA editor and I'll need a new subroutine for this let's call it sub copy film list and let me should give myself a bit more space at the bottom the screen to push this up there we go so you can read a bit more clearly and I'm going to start by selecting the appropriate worksheet or going to worksheets sheet 1 at least a worksheets sheet 1 dollar activate once I'm there all I need to do at this point it will refer to any cell that I know is in my block of film data so usually you use the top left hand corner so that's usually where you starts so I know that the top left hand corner of my blog is range a 1 followed by a full stop and then the property that I'm looking for is called current region current region refers to the entire block of data in which cell a1 sits and I could say doc current region dot select but what I want to is dr. in region dot copy now when I've copied something there are several ways to put it into another position I guess the obvious way to do it I think is as though you were doing this manually in Excel what you would have to do is then go to a different worksheet if you were trying to pipe pop it into a different worksheet so I have a sheet two products of eight and then you would have to select to sell and you would usually right click and paste or maybe you can press ctrl + V so I need to refer to another cell so I'm going to say range a1 dot and then a method that I'm looking for this time is called paste special there's no single separate a paste method for a range object in excel vba is always paste special if you wanted to provide some extra information to the paste special method there are some actual arguments if you type in a space after the word paste special you'll see that has a list of extra parameters as paste as paste type by default it will paste everything but you can scroll through the list of constants there and see exactly which thing you want to paste so I'm just going to paste all which means I don't need to specify any extra arguments just paste special by itself we'll do that so if I quickly look at what goes on there if I just Recep through this regime I should find that I use f8 to step through it's actually 1 it copies the current region you can see the other dotted outline there to prove that's happened goes to sheet 2 and then I've forgotten that I'd added then these extra extra rows on sheet 2 earlier on I'll just paste directly over the top of those so there we go so obviously it doesn't change the column widths unfortunately so as to have the next little problem that will solve probably the easiest way to solve the column widths problem is just to paste in the column widths immediately after you pasted all the data so as I said range a1 that paste special below that I can also say range a1 dot space special again and this time specify that I want to paste in the column widths it's unfortunately you have to do this in two separate steps but that is the case so if I've if I've modified my sublet in there again maybe I'll this time I'll go to sheet 3 just to go into a blank sheet rather than pacing over the top of what I've already got I'll step through the routine so I can use f8 step through sheet 1 it will be activated I'll copy that cell that region of data then go to sheet 3 paste in all of the data and then paste over with the column widths and that will solve the problem and make sure our data looks nice and sensible there is another method for copying data in Excel VBA as well I'm going to demonstrate this I'm going to add a new worksheet first of all so I've got so much paste my data and then back to the VBA editor and I'm going to copy most of this subroutine actually I'm just copy the entire subroutine to begin with and then I'm going to delete a whole bunch of lines out of here I'm going to delete everything except for going to worksheet one and copying the current region from range a 1 I'll also need to modify my subroutine name you can't have two subroutines with the same name in the same scope so that's good let's call this a method to a nice inventive name so once we've copied a cell what I can also do is provide an extra argument or an extra parameter for the copy method there's an optional argument or an optional parameter called destination so this is simply a reference to a cell that you want to start pasting that data in or copying it to so all I need to do is referred to let's say I want to refer to a worksheet the one that I've just added which was called sheet 4 so worksheets sheet 4 and then the cell that I want to copy that to is range a1 again so this saves me having to go to another worksheet I can just indirectly refer to range a one on another worksheet and send my copied cells to that sheet so if I once again just demonstrate this by stepping through use f8 to select which you want to activate worksheet one and then copy that block of cells sending the data to another worksheet you almost can't see that it's happened in Excel itself there's even there's no indication that I've copied any data from from from sheet one but if I go to sheet 4 I should see an indeed ID that all my data is there of course it's a little bit more awkward now to change the column headings I can't just paste special the other column widths sorry column headings I can't just paste the column widths over there cells that I've just them just pasted so need another method for changing the column widths so let's add in the lines of code that will change the widths of columns B and C back to the VB editor and now you know well first of all changes scream with so I have another limit to earth to show yes and in my film list method to copy film is method to subroutine I'm going to add a couple of extra lines here first of all I'm going to cheat copy the the worksheets activate line and make sure we've gone on to worksheets sheet 4 then I've got several choices here for how I could select or defer to columns B and C there's a way to do this using the range range property so I can say range if I want to refer to just column B I could refer to be column B and I could say dot select or doc copy or whatever you want to refer to range columns B and C I can simply update the add the reference to range B colon C I can also do it actually and using another property called columns so I refer to columns again I can refer to B by itself but I can refer to it in a slightly more short syntax by just referring to the letter B so rather than B : B it's just B if I want to refer to more columns that I can extend that syntax again and refer to B : C or whatever other column I wanted to refer to what I can then do is either change the width of the cells or I can apply a method that will change the width automatically so if I wanted to change the width manually to an explicit value I could refer to the width property notice again unfortunately the columns property doesn't provide you with the intellisense list so you have to know what property to type in so I could say dot width I could say make it equal to an explicit value of 20 but that's not particularly useful because I don't know how wide my columns need to be so rather than changing the width property directly what I can do is apply a method called also fit so if I were to demonstrate this now by first of all changing the the width of my screen and using f8 to step through this I'll go back to sheet 1 and then copy the data again and then go back to sheet 4 but this extra line that we've added in here will change the widths of columns B and C to be exactly as wide as they need to be so there's the the changed columns now I mentioned before that I could refer to columns B and C using the range property as well there is one slight extended thing you would need to do in order to make the autofit method work I'm going to comment out my columns line and I'm going to replace that by referring to range B : C dot now you'll see that in the intellisense list there is an auto fits method available but unfortunately it won't work directly by referring to range B and C like this to use auto fit I have to refer to an entire column and that's what the columns property does the columns returns a range of cells which forms an entire column of cells so if I'm using the range property to do to do this I need to first of all refer to the entire column property of that range and then I can refer to the autofit method to achieve the same results so if I very quickly pop back into Excel and let's let's say I want to insert a new worksheet first of all so I'll insert sheet 5 and back in the VBA editor I'll update my references to sheet 4 so they go to sheet 5 just to prove that it's working and it's doing this on a brand new sheet if I step through the routine again go to sheet 1 copy this data into c5 then look at sheet 5 and then finally change the widths of columns B and C using the range property instead and we end up with exactly the same result um which method should you choose should you copy and then paste special and then change column way or should you copy directly to a worksheet and then go to that sheet and then change the column widths it's really entirely up to you there's there's very little difference in it I think if you're not concerned about changing the column widths or providing any more formatting and certainly this technique where you specify the extra destination parameter of the copy method is the more more useful one and the more efficient one to go for but it's nice to know both techniques and both variations over of copying and pasting data if you've enjoyed this training video you can find many more online training resources at ww-why Zeljko UK

Channel: WiseOwlTutorials

Views: 614,543

Rating: 4.9353042 out of 5

Keywords: excel vba, visual basic, macros, cells, range, activecell, offset, end, wiseowl

Id: c8reU-H1PKQ

Length: 59min 45sec (3585 seconds)

Published: Thu May 23 2013