Excel VBA: Referring to Ranges & Writing to Cells (Range, Cells, Offset, Names)

in today's excel vba video i'm going to show you the different ways you can write to arrange or two different cells in Excel we're going to use range cells property offset defined names and in addition I'm also going to cover how you can refer to entire rows entire columns and also use out of it this video is brought to you by unlock Excel VBA and macros course where you're gonna learn how to simplify your work and how to save time by automating complex and routine text we're gonna go from beginner all the way to advanced for more information check out the description of this video or go to XE l plus com slash courses find out more about the course at the end of this video you might be surprised at all the different methods available but each has their own set of advantages a lot of these advantages are going to become more clear once you learn about looping in VBA this is the file that's available inside the course but you can just open up any MT excel file and follow along with me it's gonna be fun let's do this let's bring up the visual basic editor by using the shortcut key alt f11 and add a new module so just right mouse click and insert a module I'm going to call it lessons ranges yes I have option explicit here by default I covered this briefly in a previous lecture at the moment right now it's not important if you have it or not yes if you don't have it you can just leave it out I'm gonna create a new sub procedure by just typing in sub and call it refer to cells press Enter the brackets and and sub come by default before we start let's clarify one thing and that's the difference between active cell and selection let me snap in the visual basic editor here so we can view the excel file and the editor at the same time and collapses as well I'm going to use the immediate window to do a test if you don't have the immediate window activated you can do it by going to view immediate window okay so now I'm going to put a question mark and type in active cell dot address so that's the property of the active cell object I'm going to press ENTER what do you think I'm gonna get a 1 right what if I do selection dot address what will they get also a 1 now let me highlight this what am I gonna get for active cell dot address c4 okay just one cell and that's the one that's highlighted a bit differently here what about this the full range right so that's the difference active salon and selection are also liked by the macro recorder so you might have recognized it from there it's something that I don't use that often when I'm writing to self I try to avoid these methods and instead I try to refer to cells using range and cells properties here so let's have a look at a few of these one way is to use the range property I could save range and you have to use the quotation marks and put in a 1 so I'm referencing the column and the row number value equals so value is a property of the range it's the default property so even if I leave it out it will pick a dot value as the property I'm gonna type in the text first and since this is just normal text I have to put it in quotation marks and notice that I haven't mentioned the workbook here I haven't mentioned the worksheet here so which workbook and worksheet is this going to assume the active one right so whatever I have active here and I have this one active so if you're following along make sure you have the correct one active otherwise you're going to write to a different file I'm going to test this I'm going to press play and it puts first in range a 1 another way I can write this is to use the cells property it's the property of the active sheet and also the property of the range object if I type in cells 1 1 I haven't mentioned the range here so it's going to assume the active sheet that cells 1 1 and I say equals not gonna say dot value here because the default property for this one is also value and type in first now just to see it let me remove this one and let me comment this one out so the code will only run this line I'm going to press play and it puts first here that's another way of writing mm let me bring it here and comment it out here now what if I do something like this where am I going to get the second so let's check in all of these Styles right so it's a 2 till c2 and you need that Colin and those quotation marks and now what if I do something like this I have split these two different ranges with a comma you can so think about where will I see this third let's check ok I see it from A to C D there's nothing okay so that's the split and then E and F now let's try it another one range a for now check this out I'm not going to put the Colin I'm gonna put a comma C for okay so what am I going to get here only in a only and see nothing in B if I wanted something in B I can write it in this way but there's also another way of writing this putting the quotation marks like this and the comma and then the quotation marks again so we're on fifth notice here the difference between this and there I'm going to press play and it puts it in the middle as well okay so it's really important that you use the quotation marks properly to get the result that you want so take some time to take a look at the differences here and the result that you get here yet another way of writing this is to reference the column like this and combine it with the row number with an + sign okay so if I do a 6 and then we do C and 6 here the reason you need the + sign is like pretty much the same as when you write Excel formulas whenever you're combining text with numbers or texts with cell references you always need that and sign you need that in VBA as well and we're going to type in 6th here what will we get here we get it the same way like we had in here now the reason you would do this is when you learn about looping and if you want to go through many different roles because this is going to be a variable that's going to change sometimes you want it to be six sometimes seven eight nine 10 and so on and that's why you would split it in this way we get to that later on another way that you can write this is this we use the cell's property of the range object and we say six one and six three hit because six is the row here because it's the other way around and one is the column here in case we ever need to loop through many different columns and different rows it makes sense to refer to them by numbers okay so I'm gonna just take this one out delete this and let's run this and you get the same think here let's put this as a comment there so what about this one we're using the cells property of the range object where would the 7th come in so instead of highlighting one cell here I'm highlighting a range and then inside the range I am referencing cells for two okay so let's press play let's see what we get we get b7 so let's think about this we have a four to c7 highlighted so that's this range and inside this range we want cells for two so let's count first is the rows 1 2 3 4 1 2 we end up here another way now is to use the offset property so let's say I do range a1 dot offset and we saw this before when we use the macro recorder and we use the relative referencing 7 to W and I'm going to type in 8 here okay so where am I gonna get the 8th let's try right here why does it do that range a 1 I'm offsetting 7 so it starts to count from here 1 2 3 4 5 6 7 & 2 1 2 I end up here if you remember in the example where we use the relative referencing to record our macro it use the offset property as well but then it did something strange right after offset it typed in range a one stop value or range a one dot select so something like this right can be very confusing but let me just run this let me delete this one let me run this when we get the value there but we're super confused about why do we need to refer to this two times now this is just the behavior of the macro recorder and all this stands for is that we're selecting one cell in the end if I put in something like a 1 - let's put a 4 okay let's see what we get for cells right so that's pretty much what the macro recorder was doing it was only using this reference meaning it's just one cell right so you don't need it if you're recording macros you can easily remove that range a one reference and just go with doll select okay so now it would make sense let me just add a clear one to this so that we clear all the results before we write new ones for that I can just put in cells adult clear okay because that's the active sheet is assumed so active sheet dub cells dot clear and I'm going to run this deletes everything and then it runs through the macro you can also offset a range so instead of offsetting one cell you can have a range so something like this I'm starting off with this range and I'm offsetting by eight and one column okay so let's see what we get a 1 to be 1 and then I'm offsetting the whole thing by eights I move down here and one I guess I move here so basically my range was here and the whole thing was offset by this value last one is to use the name manager names that you give so for example let's say this cell here I'm gonna book more hit or give it a name called last one and I'm gonna do this by going to the name box here and typing in the name and pressing enter ok so I have given this cell this name now I can reference that name in the range address and to do that you have to put it in quotation marks as well let's do this is our last one that's the 10th ok so when I click this it writes 10th here now the advantage of this is that it's not a specific cell reference if I bring it here you see the last one has moved here I'm gonna step into the code actually so that we can see it clear to do that just click anywhere inside your code and press f8 this way you can go line by line and you press f8 you move to the next line so now we clear it the cells and we're moving and we're typing these in then if I press play it just runs through the rest of the code and we can see that 10th has moved down here if you don't want to use the shortcut key when you're debugging you can go to debug and you can step into from here I'm gonna move this now back to here okay so quickly let's have a look at how we can also reference entire rows or columns if I type in rows I'm gonna do let's say 12 till 14 now notice I need the quotation marks because I have Collins in there right so that's considered text I don't want to write to an entire row so I'm going to use the row height property and set this to 30 okay so that's 12 till 14 so the row height should get bigger let's go like this yeah we can see that expanded one way to refer to separate rows is like this this way we are just referring to 16 18 and 20 so we're skipping 17 and 19 okay so let's check that out okay we can see that 17 and 19 were skipped here now what about columns it's the same concept I'm gonna put it in quotation marks use e to F so that's the entire column and say dot column width and let's set this to 10 okay so that's e to F I'm gonna press play because of that expanded a little bit and the same way you can refer to different columns as well like this or like this okay so let's see what these do let's press play okay so H and then J dot column width is 10 so this means it's only the H column and only the J column that have a column width of 10 and when I refer to these like this like columns 1 3 column width is 5 so again this is the first column to the 3rd column if you want to auto fit all your columns you can do cells the columns dot out of it and that's going to make sure everything is fitted properly so these are the different ways that you can refer to self I've summarized them for you on one slide so make sure you go through them and you understand the difference especially when it comes to the quotation marks right so for these cases where we have the quotation works like this and when we have it like this so really understand this difference here and spend some time to get familiar with the syntax we also take a look at different ways that we can reference entire rows and columns kiss in this case we changed the row height the column width and we use the autofit since in your projects you're mostly going to be working with ranges in the next lecture I'm going to take you through the most common range properties and methods that you're going to need for your projects I hope you found this video useful if you did I'd appreciate it if you gave it a thumbs up if you're interested to find out more about my excel vba course check out the link in the description below if you like to learn about excel formulas visualization dashboards check out the videos on my channel and if you like what you see consider subscribing [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] you

Channel: Leila Gharani

Views: 210,727

Rating: 4.954525 out of 5

Keywords: referring to ranges in vba, excel vba range, excel vba tutorial for beginners, Excel vba cells, range with cell argument, XelplusVis, cells property, offset excel vba, range.value, Advanced Excel Tutorials, excel vba basics, entire row vba, excel macros, entire column vba, immediate window vba query, Excel 2016, Excel 2013, Excel 2010, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Leila Gharani

Id: acGJb9Oojho

Length: 19min 14sec (1154 seconds)

Published: Thu Apr 12 2018