welcome to this yel tutorial in this video we're going to teach you one of the most fundamental things you 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 or the 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 multip 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 cells you might have previously defined once we've covered 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 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 10 highest grossing films of 2012 and we're going to be writing sub routines which will add new films to the list so to start with we need to get into the VB 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 VB 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 M I'm going to call mine mod selecting cells so there we're ready to go we'll start with a quick look at how you can select single individual cells based on their absolute position on the worksheet so let's have a quick routine in which we can write the code that will do that so select single cells by position enter a couple of times and tab once so what I need to start doing now is selecting single individual cells if I quickly switch back into Excel itself alt and f11 on your keyboard the cells I need to select are a13 B13 and then c-13 and I need to fill in the 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 a keyword called range range is a type of object in Excel VBA and it's how Excel VBA describes any cell or block of cells is referred to as a range object and 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 round 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 if I type in the 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 IND idual 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 a quick reminder if you don't want to have to type in Long keywords you can always hold down the control key on your keyboard and press the space bar to attempt to force the Intellis sense 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 ACT cell has change its 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 we'll look at for selecting a single cell allows you to refer to a row number and a column number rather than a cell reference so what we'll do although I could write range B13 do select what I'm going to do instead is use the cells keyword cells if you want to set a parentheses after it allows you to specify a row number and a column number for a cell that you want to refer to so the row number here will be will be row number 13 follow that with a comma the column number will be column number two 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 Intellis sense and I can't press control and space to force the intell Sens 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 13 comma 2 do 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 control and space at the start of the next line look for the word active cell do value equals and then apparently the 11th highest grossing film of the year 2012 was a film called called The Lorax I can spell that properly it doesn't really matter an odd madeup 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 single cell and 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 in it cell reference without having to use the keyword range or the keyword cells you can actually in a set of square brackets type in the cell reference of the cell you want to refer to um again if you type in a full stop after you've done this you don't see any Intellis sense 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 range so C13 dot select then hit enter and I want to change the value of the newly active cell so control and space to bring up the intellisense list look for active cell type in a full stop look for the value property and I want to make this equal to a release date now according to uh to Wikipedia The Lorax was released on the second of March 2012 when you want to enter dates in VBA you need to enclose dates in a set of hash marks and UK users will need to be very very careful about typing in their dates in the in the UK date format if I typed in 2322 although to a to a person from the UK that looks like the 2nd of March 2012 that's actually February 3rd 2012 12 so yes unfortunately dates are in US 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 dates as to followed by a short three-letter code for the month followed by the 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 worth while 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 there we go so I can see the CES that I'm going to be uh be referring to and then I click anywhere inside the sub rtin I want to to execute and then press the f8 key once so each subsequent time I now press the f8 key it will execute the line that is currently highlighted so press F to reach uh range a13 do select so when I execute this line we ought to be able to see in the background that's 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 that will select cell B13 and so on and then C13 is like the unusual method which also works and then that changes the value to uh 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 on board the date formatting of your computer so so the front end of excel will use the regional settings of your computer whereas the it's just the VBA code itself which doesn't so this is I promise this is the 2nd of March 2012 not February 3rd so when I press f81 final time just to end the sub routine there we go they're 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 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 returning to the VB editor and execute this sub routine again I'm going to do this by using the Run button or pressing F5 on my keyboard this time so that should just happen as quickly as possible and hopefully you can see that this has happened in whichever worksheet I had active so it was in sheet two 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 select the relevant worksheet so if I add a line to the top of my sub routine which will which will do that for us I'm going to refer to worksheets sheet one and I'm going to say do activate now there are many many different ways to select or activate worksheets this is just one simple technique that you'll find commonly used 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 worksh that was on to begin with I change from that one into worksheet one and then change the values of the appropriate cells so if I use f8 to step through this sub routine now you can hopefully see at the bottom I'm still on sheet two if I use f8 to step through this routine when I reach this one worksheet sheet one activate I first of all move to sheet one and then subsequently select and change the values of all of those cells again you can even extend this technique of referring to a specific worksheet first um 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 holding down the control 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 so if I extend my screen down again you can see VBA project book two so what I could do in my select single cells by positions sub routine is before I refer to a specific worksheet I could also refer to a specific workbook and I can do that by saying workbooks open parentheses and double quotes book two which is it name close the ques close the parenthesis and then say do 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 file name extension here as well so for example if I was referring to my top movies 2012 I'd have to extend xlsm 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 one if I pop back into the VB editor again with alt and 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 fact you'll see this a bit more clearly if you can see the title bar at the top 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 makes 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 there'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 changed the value of the cell we've just selected but in VBA it's possible to change the value of a cell without having to select it first so I'm going to demonstrate that with a quick new sub routine which adds a new film to the ne row which will be row number 14 so sub change cell values without selecting them I should picked shorter shorter subre name shouldn't I pick chain sale values without selecting get 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 your cells you can either use range cells or the shorthand way I'm going to revert to using range mainly because I get the Intellis sense available with the range keyword again I can use my Intellis sense at the start of the uh the line I can press control and space and look for the word range in the list and then open it out of round s brackets and double quotes and this time the first cell I want to refer to is A14 close the double quotes close the 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 if I type in the 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 A14 value equals and then the next number in the list with the number 12 and then hit enter and I can carry on in that same vein I can say range b14 sorry 14 value do value try again equals and then the next film in the list according to uh to Wikipedia was Wreck It Ralph that was the 12th 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 c14 value equals and the release date for recit Ralph was the 2nd of November so I'm going to type that is to nov 2012 close to the hash mark I'm when I hit enter at the end of the line I'll know that the number for the month and the number for 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 sub routine 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 as if by Magic almost the values 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 a cell using a technique like this again hopefully you'll notice it's implied that the cells you want to change are on the current 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 uh sheet two dot range A14 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 a specific worksheet without having to have either of those things selected so if I step through this routine just to prove that I'm running it and then what I would have to do then is manually go away and check sheet to to ensure that those values 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 three this time back to the VB editor and at the start of each of these lines I could refer to workbooks book three dot worksheets sheet two and then the specific range object that I want to refer to there again I I'll copy this to to the beginning of each of the other lines back in Excel I'll just quickly make sure that I've gone back to my original um 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 two I'll see that those those specific cells have been filled in from a completely different workbook so you can hopefully see that this sort of technique can make your code 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 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 sub routine I'm going to call it sub select multiple cells and I'm going to give myself a bit more space below this one just to push up the code 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 wanted to refer to is A1 and the end of the block of cells I want to refer to C1 so if you separate those with a colon like so close the quote and brackets do 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 ever 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 provided with any Intellis sense with the sele action keyword so what I'm going to do is choose to modify the interior property and I'm going to 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 let's see I'm going to change it to a to a background color I'll display my Intellis sense list with control and space and look for one of my RGB colors so some kind of 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 it's 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 it's going to be the font property and hopefully clearly the advantage of this is that you don't lose the Intellis sense 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 a cell reference inside a set of square brackets so once again to refer to the same block of cells A1 to C1 I can simply do do this A1 to C1 instead 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 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 let say 14 and there we go I think that's probably enough formatting for the first row so let's move on to the to row number two which if I just quick 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 um back into the VB editor I'm going to show you a slightly different syntax of the uh the range technique so if I 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 can make out the tool tip that's appearing on screen here the range property technically has two separate arguments which allow you to refer to effect 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 sell A2 I can type in a comma and then in its own set of double quotes again I can type in C2 so that effectively refers 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 let's see a a light blue this time I think do we have a light blue there it 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 used 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 we're going to use this one again several more times in this video this syntax would also be the only way we could incorporate cells to refer to multiple cells uh unfortunately you can't use cells by itself to refer to a block of cells or 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 property so what we would have to do is say range open parenthesis and then cells open parenthesis again just move the mouse curser the first cell I want to refer to is cell A1 sorry A2 so that's row two column 1 close one set of parenthesis then another comma and cells in this case the row number would be two again and the column would be three so that's C2 close a parentheses for cells close another set for range and then I can say dots fonts dots color perhaps equals oh I don't know uh let's go for 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 uh 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 job that it's meant to do so let me drag the screen down so we can see the cells we're going to be affecting and then we'll step through this one just so we can see that all the things we expect to happen actually are happening so range A1 to see1 select so press f8 and there we go we see those cells 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 Al together switch back to the VB editor and press f8 again and we'll see the font changes to White regardless of whether it was selected or not and faas again just to prove that the the other method works that increases our font size and then range A2 to C2 we're going to modify the uh the interior color of those and then finally we're going to change the font color of the of those cells to dark blue fa one more time on nsub to make sure that we we finish running our sub routine and those are all the techniques Al most of the basic techniques at 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 names in several different ways I I don't want to spend too long doing this because 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 that 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 what I'd like to do is 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 have 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 the Mouse as a link you'll see there's also a keyboard shortcut for this control and shift and F3 But whichever the two two options you 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 check boxes 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 a labels in the top row of my selected cells to create three range names so when I click okay 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 the column headings have been replaced 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 range name that you want to remove and then simply click the delete button I'm not going to delete mine now because of what I want to do is 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 sub rting for this sub refer to range names and we're going to use the range technique once again to begin with so let's refer to our range of uh 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 do select or in fact let's just change some sort of fonts proper say font. italic equals true so that will refer to the the ID range you can also use the Shand technique to refer to a range name so if I open a set of square brackets and the next column was title close to 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 sub routine a quick test to check that it works so 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 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 there that goes f8 one more time to end the sub routine 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 so we either need to know it cell reference or its range name or it's row and 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 will add in a new film to whichever row is the next blank one in our list the methods we'll use will work basically like this we'll start by selecting cell a one or referring to cell A1 as we know that's a known fixed starting point from there we'll simulate getting down to the end of the list in a downwards Direction you can 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 cell with the next number in the sequence and then we can populate the cell one column to the right with the name of the film and the cell two columns to the right with the release date of that film so what we need to do is work out how to make that work in VBA terms so let's start by going back to the VB 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 to call it mod uh relative select and a new Sub rutin in here which will 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 sub routine so the first lineer code is actually going to select or activate sheet one to make sure we're in the correct correct place so worksheet sheet 1 do activate then we know 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 going to use my range technique so range A1 do select now from here what we need to do is move down first of all to the end of the list in a downwards Direction so from the cell that I've just referred to or selected 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 spef 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 unfortunate that the one that we want is hidden beneath the tool tip but if I use the the cursor keys to start scrolling through the list I'll find the one that I want appears Excel down so Excel down close the parenthesis and finally I need to say I need to do something to the cell that I've just referred to so I either 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 one activate we won't see anything happen here because we're already on that worksheet range a1. select we already know what that will do this is the important line here what we're going to see happen is that the next cell that 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 it's 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 or right so I'm going to provide a zero for this parameter close the parenthesis and once again just like with the end property I need to 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 select the worksheet and cell A1 then end Excel down takes me to the end of the list offset one moves 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 where I 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 at the end of the list and then one cell further down we can actually do that in one single line so let's start by referring to range A1 then rather than selecting that cell I want to refer to its end property and I want to specify the Excel down direction for the end property following that I don't want to select the sell at the boss 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 a 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 do select 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 so there's only one single select method used here which is way more efficient when you come to run code like this and it's always the preferred approach I think that you uh 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 uh very impressive when I quickly execute it because we'll just end up in at the bottom of this the 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 the 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 active cell and 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 of the cell that is one above the active cell so active cell do offset min-1 comma 0 dot value all I need to do 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 L I'm going to start with the word active cell and I'm going to use the offset property again I want to go zero rows up or down this time but I want to refer to the cell that is one column to the right so that's a positive number use negative numbers to move left if I close the 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 and apparently according to my information that was the film Lincoln which again I've not seen that one either um so that will change the value of that cell to be 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 I need to refer to the cell is two columns to the right and the date or the release date of Lincoln was 9 no 2012 so there we go that's how you refer to cells based B on relative positions so you use end to jump to the top bottom left or right of a continuous block and you 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 sub routine 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 starts with selecting the next Blank cell which we knew that one worked already and then the next number should be 13 which is one more than 12 and linkoln should go into column B and that release date should go into column C just like so f8 one more time to make sure the sub routin is 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're going to 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 the 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 at least 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 control and 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 sub routine which we'll call Select uh let's call it variable column or call for short so when we're using this technique we're going to use uh one of the syntaxes of the range technique so we're going to start by referring 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 uh whatever it was A113 or 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 2 parameter what I'm going to do instead is say range open parenthesis and then essentially what we did here from range a1. end XEL down now we saw previously that this referred to the Cell at the end of The Continuous list so if I replicate that here with a1. end Excel down parentheses rather than a number n that would help that entire set of code there refers to the cell that is at the end of the list regardless of which one it is so all I need to do now is close the exra 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 A3 and the bottom cell as whichever cell is at the end of the list from cell A1 so when I 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 any number of extra items to 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 values 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 say selection do font do 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 select we could simply say font. italic equals true at the end of this line finally if we execute this entire sub routine in one go just use the F uh the F5 key or click the green triangle on the toolbar we ought to find that entire block of cells gets selected and the font is changed to italic now I can use almost exactly the same technique to uh to color in the the font of column b as well so what I would want to do just quickly switching back into Excel is change everything from cell B3 down to the end of the list now the technique I used for column A was to go from cell A3 and then the second cell I referred to as a1. endex L down so essentially what I did was from cell A1 it was like holding on the control key and pressing the down arrow key now 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 demonstrate 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 for for formatting the the fonts of column B is we're going to go from cell B3 and then the second cell we'll refer to is b2. endex Cel down and that will take us to the last populated sell in the list there's one thing that's is really worthwhile pointing out this Tech this technique with 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 let's go back to the VB editor and I can pretty much just copy and paste most of this line already so if I copy and paste that line just resize the screen so I've got 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 endex cell down and I'm rather than selecting 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 we ought to see if I'll just run it through and one one go I should see that the entirety of column B now gets its font changed 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 control shift and tap the down arrow key and then control shift 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 so if I go back to the VB editor and let's write this as 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 do end XEL down first of all and I can simply then tag on another end property to the end of this so do end Excel to WR close two sets of parentheses and then say do 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 that it gets selected as well and then I could maybe say selection sorry selection do interior do color equals and then 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 extra 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 gets selected and that's exactly what what happens so if I use f 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 is you can just I can just make out 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 a known starting point to the end of a list regardless of how long it is or how wide it is 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 let you do that select any cell within a continuous range and press control and 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 VB editor and I'll need a new sub rtin for this let's call it sub copy film list and let me just give myself a bit more space at the bottom of the screen to push this up there we go so you can read it a bit more clearly and I'm going to start by selecting the appropriate worksheet or going to worksheets sheet one at least so worksheets sheet one do activate once I'm there all I need to do at this point is refer to any cell that I know is in my block of film data so usually you use the top left left hand corner so that's usually where you start so I know that the top left hand corner of my block is range A1 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 current region do select but what I want to do is do current region do 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 p pop it into a different worksheet uh so I go for Sheet two. activate and then you would have to select a cell and you would usually right click and paste or maybe press control and V so I need to refer to another cell so I'm going to say range A1 Dot and then the method that I'm looking for this time is called paste special there's no single separate paste method for a range object in Excel VBA it's always paste special if you wanted to provide some extra information to the paste special method there are some extra arguments if you type in a space after the word paste special you'll see that it has a list of extra parameters there 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 that I don't need to specify any extra arguments just paste Special by itself will do that so if I quickly look at what goes on there if I Just Step through this routine I should find that I use f8 to step through it select sheet one it copies to the current region you can see the the dotted outline there to prove that's happened goes to sheet two and then oh I forgotten that I'd added in these extra extra rows on sheet two earlier on I I'll just paste directly over the top of those so there we go so obviously um it doesn't change the the column widths unfortunately so that's perhaps the next little problem that we'll solve probably the easiest way to solve the column widths problem is just to paste in the column widths immediately after you pasted all of the data so where I've said range A1 that paste special below that I can also say range A1 dot paste special again and this time specify that I want to paste in the column widths it's unfortunate that you have to do this in two separate steps but um that is the case so if I've uh if I've modified my stability in there again maybe I'll this time I'll go to sheet three just to go into a blank sheet rather than pasting over the top of what I've already got and I'll step through the routine so I can use f8 step through sheet one it will be activated I'll copy that that region of data then go to sheet three paste in all of the data and then paste over that 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 somewhere to paste my data and then back to the VB editor and I'm going to copy most of this sub routine actually let me just copy the entire sub routine uh 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 A1 I'll also need to modify my subroutine name you can't have two subroutines with the same name in the same scope so let's let's call this uh method two nice inventive name so once we've copied um 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 refer to let's say I want to refer to a worksheet the one that I've just added which was called sheet four 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 A1 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 worksheet one or 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 uh from from sheet one but if I go to sheet four I should see and indeed I do 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 the column widths sorry not the column headings I can't just paste the column widths over the cells that I've just um just pasted so I 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 I'm going to well first will change the screen with so I have enough room to uh to show you this and in my film list method two copy film list method 2 Sub routine I'm going to add a couple of extra lines here first of all I'm going to cheat copy the uh the worksheets activate the line and make sure that we've gone onto worksheets sheet four then I've got several choices here for how I could select or refer to columns B and C there's a way to do this using the range uh range property so I could say range um if I want to refer to just column B I could refer to B column B and I could say do select or do copy or or or whatever if I want to refer to range columns B and C I can simply update the uh the reference to to range B Colon c I can also do it actually um using another property called columns so I refer to columns again I can refer to uh 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 colon B is just B if I want to refer to more columns then I can extend that syntax again and refer to B Colon c or whatever other column I want refer two 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 uh property doesn't provide you with the intell sense list so you have to know what property to type in so I could say dot width and 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 autoit so if I were to demonstrate this now by first of all changing the uh the width of my screen and using f8 to step through do this I'll go back to sheet one and then copy the data again and then go back to sheet four 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 uh the changed columns now I mentioned before that I could refer to columns B and C using the range uh property as well there is one slight extended thing you would need to do in order to make the auto fit method work I'm going to comment out my columns line and I'm going to replace that by referring to range B colon c dot now you'll see that in the Intellis sense list there is an autofit method available but unfortunately it won't work directly by referring to range B and C like this to use autoit 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 let's say I want to insert a new worksheet first of all so I'll insert sheet five and back in the VB editor I'll update my references to sheet four so they go to sheet five 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 one copy this data into sheet five then look at sheet five and then finally change the width of columns B and C using the range property instead and we end up with exactly this same result um which method should you choose should you copy and then paste special and then change column widths 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 then 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 of uh of of copying and pasting data if you've enjoyed this training video you can find many more online training resources at www. yl. co.uk