Hey, Scripting Guy! How Can I Determine the Value of the Last Cell in an Office Excel Range?

ScriptingGuy1

Hey, Scripting Guy! How can I determine the value of the last cell in a range like C1:C10?

— MJdR

Hey, MJdR. You know, just a few days ago word leaked that a Federal Emergency Management Agency (FEMA) press conference held to discuss the US government’s response to the recent spate of wildfires in California was a fake. Instead of having real reporters asking real questions the press conference consisted of FEMA employees asking phony, pre-screened questions. That led to some major concerns and worries here in the US: after all, if FEMA press conferences are phony, does that mean that Scripting Guys press conferences are phony as well? Because so many people are losing sleep over this matter, we’d like to take a moment to put your fears to rest. Have no fear, America: the Scripting Guys press conferences are as real as they’ve ever been.

Now, let’s take a moment to answer a few questions from the audience:

Thank you, PC; we appreciate that. How about the gentleman over there:

Again, thank you for those kind words, DT. In answer to your question, no, we don’t find it difficult in the least. After all, we’re doing what we love, and we couldn’t ask for anything more than that. How about the lady with the angry snarl on her face:

That’s a very good observation, Scripting Editor. However, we believe that MJdR asked a question before you did, something about determining the value of the last cell in a range. In all fairness, we should address his question first, then get back to you if time allows. Here you go, MJdR:

Const xlCellTypeLastCell = 11

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1, 1) = 1
objWorksheet.Cells(2, 1) = 2
objWorksheet.Cells(3, 1) = “”
objWorksheet.Cells(4, 1) = 4
objWorksheet.Cells(5, 1) = “”
objWorksheet.Cells(6, 1) = 6
objWorksheet.Cells(7, 1) = 7
Set objRange = objExcel.Range(“A1:A7”)
objRange.Cells.SpecialCells(xlCellTypeLastCell).Activate
Wscript.Echo objExcel.ActiveCell.Value

Any further questions?

Um, we mean besides yours, Scripting Editor.

Ah, right: how does this script work? Unlike some of the other questions we’ve been asked recently, that’s a very good question.

To begin with, we define a constant named xlCellTypeLastCell and set the value to 11; we’ll use this constant to tell the script which cell in the range we want to maneuver to. (Needless to say, that would be the last cell in the range.) After defining the constant we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Excel that we can see onscreen. And once we have a running instance of Excel that we can see onscreen, that frees us to use these two lines of code to create a new workbook and then bind ourselves to the first worksheet in that workbook:

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

What follows next are seven lines of code that simply assign values to cells A1 through A7; that means you don’t have to worry too much about any of those lines. You might have noticed, however, that we actually assigned cells A3 and A5 empty strings. Why? Well, as you probably already know, Excel can sometimes be “tricked” into thinking that it’s encountered the end of a range simply because it encounters a blank cell somewhere in that range. We tossed the blank cells into our range just to demonstrate that the script will work even if a range has a blank cell or two.

Thank you, but please hold your applause until the end. Thanks again.

That brings us to these two lines of code:

Set objRange = objExcel.Range(“A1:A7”)
objRange.Cells.SpecialCells(xlCellTypeLastCell).Activate

In the first line, we’re creating a Range object that encompasses cells A1 through A7; that should be fairly straightforward. So then what are we doing in line 2? Well, in line 2 we’re doing a couple of things. First, we’re calling the SpecialCells method, a method that belongs to the Range object’s Cells collection. The SpecialCells method is deigned to return a collection of, well, special cells; this could be all the blank cells in a collection; all the cells that contain the same formatting; all the cells that contain a formula; and so on. (For more information, see the Excel Language Reference on MSDN.) In our case we’re passing SpecialCells the constant xlCellTypeLastCell; this tells the method to return a collection containing a single cell: the very last cell in the range.

OK, that’s one thing that we do with that line of code: we get back a collection containing the very last cell in the range. The other thing we do is call the Activate method, something that – once we’ve identified the last cell in the range – makes that cell the active cell.

Why do we bother activating the last cell in the range? That’s easy; that way we can echo back the value of the cell by using this line of code:

Wscript.Echo objExcel.ActiveCell.Value

By using the ActiveCell property we can retrieve the value even if we don’t know the address of the last cell in the range. (Which, in a real script, is likely.)

Now, what do you suppose we’re going to get when we echo back the value of the active cell? You got it; we’re going to get back the value of cell A7, the last cell in the range:

7

That’s all we have to do. Now you can applaud.

Oh, wait: we still need to answer the Scripting Editor’s question, don’t we? As it turns out, there’s nothing suspicious here at all. To begin with, Peter Costantini’s initials are not PC; for reasons known only to himself, Peter actually uses Y6 as his initials. Likewise, Dean Tsaltas’ initials are not DT, either; being a Canadian citizen, Dean does not have any initials. We hope that clears up any misconceptions.

That concludes today’s press conference. Thank you all for coming, and please drive home safely. We’ll see everyone tomorrow, same time, same place.

ScriptingGuy1

Follow