Existing Excel - Cell Range

Quick reference

• 00:04 Okay, in the last video we looked at grabbing a specific cell.
• 00:07 In this video we want to look at grabbing a range of cells.
• 00:11 So we've got our same code from the last video where we imported our things and
• 00:14 we're loading our pizza.
• 00:16 Workbook and setting the active worksheet.
• 00:18 So now we want to grab a range of cells.
• 00:20 So let's take a look at our Excel spreadsheet real quick and let's say,
• 00:25 we want to grab A2 through A8.
• 00:27 We just want to grab these names and
• 00:29 there are different ways we can do this with columns.
• 00:32 But in this video, we're just going to look at grabbing ranges of specific cells.
• 00:36 So to be able to do that, we can use something called the cell range function.
• 00:39 So let's head back over to our code.
• 00:41 And let's create a variable called sell underscore range.
• 00:45 And let's set that equal to whatever we want to get.
• 00:48 So let's grab Ws is the worksheet.
• 00:51 And now let's pass in something called a Python slice we can slice using a colon.
• 00:57 And now what does our slice want to be what we want to be A2 through A8.
• 01:05 And if we look back at our Excel spreadsheet, that would be A2 through A8.
• 01:11 So John and through Jason.
• 01:14 Now let's print out our range.
• 01:19 So let's call our print function and let's just pass in cell underscore range.
• 01:23 All right, so let's save this.
• 01:26 Now we know this will probably return a bunch of objects, right?
• 01:30 Because we have to call the dot value.
• 01:33 But before we do that, let's grab all of this and copy it, and then bring it back
• 01:37 over here and just kind of paste it in here to look and see what this is.
• 01:42 Now we can see these are round brackets that parentheses, so
• 01:45 we know this is a tupple.
• 01:46 And we can tell that this is actually several tupples.
• 01:48 This is a tupple on the outside and then inside,
• 01:52 each object that it's returning is in itself in another tupple.
• 01:58 Right, so this is kind of weird.
• 01:59 So in order to get this information, we need to loop through this tupple.
• 02:03 So let's go for cell in cell underscore range.
• 02:10 Let's just print cell just to see what we've got here.
• 02:13 So let's delete this.
• 02:16 And we can comment out that.
• 02:18 So okay, let's save this and run it.
• 02:23 So you'll notice we're printing out a bunch of different tuples, right?
• 02:26 And inside of each tuple is our cell.
• 02:29 And we know from our lessons in Python lists and tuples, we can access
• 02:33 the first item of a tuple, which would be this guy and this guy and this guy.
• 02:39 By calling the zero with item of that tupple.
• 02:42 So, to do that, let's head back over to our code and instead of calling,
• 02:45 instead of printing out cell, let's print out the zeroeth item of each of these
• 02:49 temples, by slapping on brackets and a zero.
• 02:51 So now if we save this and head back over here and
• 02:54 run this again Now we're just printing out those cells.
• 03:00 And they're just the objects as we saw in the last video.
• 03:03 So we can dot value these to get the actual values from the cells.
• 03:08 So let's go ahead and do that.
• 03:09 So we just come over here and .value.
• 03:12 Python is an object oriented programming language.
• 03:14 That means you can do object things.
• 03:16 And to do object things, you always just sort of string them along with periods.
• 03:20 So we've dot this and we got this.
• 03:23 And we can dot other things if we wanted to as well, but
• 03:26 we just want to grab the value.
• 03:27 So let's go ahead and save this.
• 03:29 And let's run this.
• 03:30 And finally, we get John Mary, Steve, Tina, Wes, Amy and Jason.
• 03:36 So we grabbed this slice right here.
• 03:39 You can slice anything.
• 03:40 You could go this way.
• 03:41 We could go, A1 through E1, we could go A1 through.
• 03:47 Now if we save this, it's likely to give us a different output.
• 03:51 And in fact, let's clear the screen.
• 03:53 And as you see it's just returning one thing.
• 03:58 That's because the output is different.
• 04:00 The tupple is returning a different things.
• 04:03 We have to go through this whole process again.
• 04:06 So we would start out by getting reading that, save this, come back here, run it.
• 04:11 See here's our new tupple.
• 04:15 So let's copy this and sort of take a look at it.
• 04:19 So we're getting a tupple.
• 04:20 And inside of that is another tupple with two items.
• 04:26 So we can call this tupple.
• 04:29 It is the zero with item of this tupple, the outside tupple.
• 04:33 So we could go cell range, and then call the zero with item here.
• 04:39 Let's save this and run it.
• 04:40 And now we're getting another tupple with things.
• 04:45 So now we can loop through that tupple to get our answers So
• 04:50 let's go, let's comment this out and let's go for
• 04:55 sell in sell underscore range, but with a zero on it, now we can print our cell.
• 05:03 Save this, run it.
• 05:07 We're getting into to actual values in object form.
• 05:10 And now we can .value those.
• 05:14 Run this again, and we get names and favorite pizza.
• 05:17 So that's the slice of A1 to B1,
• 05:20 which if we look at our thing is just A1 and B1, right?
• 05:24 So these are how you can grab different ranges of cells.
• 05:28 A little bit complicated with all the four looping, but
• 05:31 not too difficult once you've done it a couple of times.
• 05:33 In the next video we'll look at grabbing entire columns and column ranges.

Lesson notes are only available for subscribers.