{"id":63673,"date":"2007-11-02T00:12:00","date_gmt":"2007-11-02T00:12:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/11\/02\/hey-scripting-guy-how-can-i-determine-the-value-of-the-last-cell-in-an-office-excel-range\/"},"modified":"2007-11-02T00:12:00","modified_gmt":"2007-11-02T00:12:00","slug":"hey-scripting-guy-how-can-i-determine-the-value-of-the-last-cell-in-an-office-excel-range","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-determine-the-value-of-the-last-cell-in-an-office-excel-range\/","title":{"rendered":"Hey, Scripting Guy! How Can I Determine the Value of the Last Cell in an Office Excel Range?"},"content":{"rendered":"

\"Hey, <\/H2>\n

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

— MJdR<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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\u2019s 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\u2019d 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\u2019ve ever been. <\/P>\n

Now, let\u2019s take a moment to answer a few questions from the audience:<\/P>\n\n<\/THEAD>\n\n\n
\n

Hey, Scripting Guy! I don\u2019t really have a question, I just wanted to let you know that I think your daily column is not only the best daily scripting column I\u2019ve ever read, but also the best column of any<\/I> kind I\u2019ve ever read. I just wanted to thank you for all the hard work you\u2019ve done. I know the word \u201cgenius\u201d is grossly overused these days, but if the shoe fits \u2026.<\/P>\n

— PC<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Thank you, PC; we appreciate that. How about the gentleman over there:<\/P>\n\n<\/THEAD>\n\n\n
\n

Hey, Scripting Guy! As I understand it, you\u2019ve been writing this daily column for over three years now. After all this time, do you find it difficult to continue turning out columns that are so incredibly well-written, informative, and entertaining?<\/P>\n

— DT<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Again, thank you for those kind words, DT. In answer to your question, no, we don\u2019t find it difficult in the least. After all, we\u2019re doing what we love, and we couldn\u2019t ask for anything more than that. How about the lady with the angry snarl on her face:<\/P>\n\n<\/THEAD>\n\n\n
\n

Hey, Scripting Guy! Don\u2019t you find it a little suspicious that PC has the same initials as Scripting Guy Peter Costantini, and that DT has the same initials as Scripting Guy Dean Tsaltas?<\/P>\n

— The Scripting Editor<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

That\u2019s 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:<\/P>

Const xlCellTypeLastCell = 11<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.Cells(1, 1) = 1\nobjWorksheet.Cells(2, 1) = 2\nobjWorksheet.Cells(3, 1) = “”\nobjWorksheet.Cells(4, 1) = 4\nobjWorksheet.Cells(5, 1) = “”\nobjWorksheet.Cells(6, 1) = 6\nobjWorksheet.Cells(7, 1) = 7<\/p>\n
Set objRange = objExcel.Range(“A1:A7”)\nobjRange.Cells.SpecialCells(xlCellTypeLastCell).Activate<\/p>\n
Wscript.Echo objExcel.ActiveCell.Value\n<\/PRE>\n
Any further questions? <\/P>\n
Um, we mean besides yours, Scripting Editor. <\/P>\n
Ah, right: how does<\/I> this script work? Unlike some of the other questions we\u2019ve been asked recently, that\u2019s a very good<\/I> question.<\/P>\n
To begin with, we define a constant named xlCellTypeLastCell and set the value to 11; we\u2019ll 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<\/B> object and then set the Visible<\/B> property to True; that gives us a running instance of Excel that we can see onscreen. And once we have<\/I> 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:<\/P>
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
What follows next are seven lines of code that simply assign values to cells A1 through A7; that means you don\u2019t 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 \u201ctricked\u201d into thinking that it\u2019s encountered the end of a range simply because it encounters a blank cell somewhere in that range. We tossed the blank cells into our<\/I> range just to demonstrate that the script will work even if a range has a blank cell or two.<\/P>\n
Thank you, but please hold your applause until the end. Thanks again.<\/P>\n
That brings us to these two lines of code:<\/P>
Set objRange = objExcel.Range(“A1:A7”)\nobjRange.Cells.SpecialCells(xlCellTypeLastCell).Activate\n<\/PRE>\n
In the first line, we\u2019re creating a Range<\/B> 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\u2019re doing a couple of things. First, we\u2019re calling the SpecialCells<\/B> method, a method that belongs to the Range object\u2019s Cells<\/B> 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<\/B><\/A> on MSDN.) In our case we\u2019re passing SpecialCells the constant xlCellTypeLastCell; this tells the method to return a collection containing a single cell: the very last cell in the range.<\/P>\n
OK, that\u2019s 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<\/B> method, something that \u2013 once we\u2019ve identified the last cell in the range \u2013 makes that cell the active cell.<\/P>\n
Why do we bother activating the last cell in the range? That\u2019s easy; that way we can echo back the value of the cell by using this line of code:<\/P>
Wscript.Echo objExcel.ActiveCell.Value\n<\/PRE>\n
By using the ActiveCell<\/B> property we can retrieve the value even if we don\u2019t know the address of the last cell in the range. (Which, in a real script, is likely.)<\/P>\n
Now, what do you suppose we\u2019re going to get when we echo back the value of the active cell? You got it; we\u2019re going to get back the value of cell A7, the last cell in the range:<\/P>
7\n<\/PRE>\n
That\u2019s all we have to do. Now<\/I> you can applaud.<\/P>\n
Oh, wait: we still need to answer the Scripting Editor\u2019s question, don\u2019t we? As it turns out, there\u2019s nothing suspicious here at all. To begin with, Peter Costantini\u2019s initials are not<\/I> PC; for reasons known only to himself, Peter actually uses Y6 as his initials. Likewise, Dean Tsaltas\u2019 initials are not DT, either; being a Canadian citizen, Dean does not have any initials. We hope that clears up any misconceptions.<\/P>\n
That concludes today\u2019s press conference. Thank you all for coming, and please drive home safely. We\u2019ll see everyone tomorrow, same time, same place.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019s response to the recent spate of wildfires in California was […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[711,48,49,3,5],"class_list":["post-63673","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019s response to the recent spate of wildfires in California was […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63673","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=63673"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63673\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=63673"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63673"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63673"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}