{"id":67953,"date":"2006-02-15T14:01:00","date_gmt":"2006-02-15T14:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/02\/15\/how-can-i-determine-the-last-row-in-an-excel-spreadsheet\/"},"modified":"2006-02-15T14:01:00","modified_gmt":"2006-02-15T14:01:00","slug":"how-can-i-determine-the-last-row-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-determine-the-last-row-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I Determine the Last Row in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I determine the last row in an Excel spreadsheet? I can\u2019t append data to the file unless I know which row to start with.

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

Hey, TGW. You know, this is the kind of thing that separates the Scripting Guys from the rest of the world. Most people would do the same thing you\u2019re doing: looking at their spreadsheet and thinking, \u201cOK, so how do I know where to start? After all, I want to make sure I do this right.\u201d That\u2019s not the kind of question you\u2019ll ever hear a Scripting Guy ask. Instead, a Scripting Guy is more likely to go ahead and run the script and then say something like this: \u201cUh-oh: I wonder if I really should have done that.\u201d <\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. And, yes, we are<\/I> thinking of the time one of the Scripting Guys wrote a script that opened a text file and replaced the existing contents of that file with a bunch of new data. The problem? Without thinking – and without bothering to check his work – this Scripting Guy inadvertently typed in the name of his script as the text file to open. His script ran, dutifully opened the .vbs file, and replaced all of his unbacked-up script code with a bunch of data that was supposed to be stored in a separate text file. Bye-bye .vbs file.<\/P>\n

And, no, he wasn\u2019t particularly upset. After all, for that Scripting Guy it was just another day at the office.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Ironically, even though the Scripting Guys tend to be a bit impulsive and irresponsible we actually know how to do things the right way; it\u2019s just that we don\u2019t bother to do things the right way. But you don\u2019t have to be like us (thank goodness!). Instead, you can use a script like this one, which will position the cursor in the next available row in an Excel spreadsheet:<\/P>

Const xlCellTypeLastCell = 11<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\nobjWorksheet.Activate<\/p>\n
Set objRange = objWorksheet.UsedRange\nobjRange.SpecialCells(xlCellTypeLastCell).Activate<\/p>\n
intNewRow = objExcel.ActiveCell.Row + 1\nstrNewCell = “A” &  intNewRow<\/p>\n
objExcel.Range(strNewCell).Activate\n<\/PRE>\n
Before we explain this all let\u2019s show you what our sample spreadsheet looks like:<\/P>\"Microsoft \n

Now, we didn\u2019t show you this sample spreadsheet because we\u2019re so proud of it. (Although, come to think of it, it is<\/I> pretty nice, isn\u2019t it?) Instead we wanted to point out that it\u2019s a spreadsheet that includes some blank rows. Is that important? It can be. For example, it\u2019s possible to mimic the use of the Ctrl+Down Arrow using a script. That can help you determine the last row in a spreadsheet \u2026 provided, of course, that you don\u2019t have any blank rows. In our sample, spreadsheet, however, a Ctrl+Down Arrow script won\u2019t work: that\u2019s because Ctrl+Down Arrow is designed to stop at the first blank row, meaning it will think row 5 is the last row in the spreadsheet. That\u2019s why we didn\u2019t<\/I> use a script that mimicked Ctrl+Down Arrow. <\/P>\n
Incidentally, that\u2019s also why we didn\u2019t just check to see if the value of cell A-whatever<\/I> was equal to nothing and call it good. In a spreadsheet like this one, some rows simply have<\/I> no value in column A.<\/P>\n
So what does our script do? Well, it starts out by defining a constant named xlCellTypeLastCell and setting the value to 11; we\u2019ll talk about what this is for in a moment. We then have several lines of code that create a running (and visible) instance of the Excel.Application<\/B> object, open the file C:\\Scripts\\test.xls, and then create an object reference to Sheet 1:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\nobjWorksheet.Activate\n<\/PRE>\n
Now it\u2019s time to roll up our sleeves and get to work. The first thing we do is issue this command:<\/P>
Set objRange = objWorksheet.UsedRange\n<\/PRE>\n
Why? We\u2019re glad you asked that. The UsedRange<\/B> is a worksheet property that represents the entire range of cells that have data in them. For example, suppose we had a spreadsheet that had some data in cell A1 and some data in cell Z37. What\u2019s the used range going to be? It\u2019s going to be a range that starts in cell A1, ends in cell Z37, and includes all the cells in between.<\/P>\n
The key here is that the used range includes the very last row in the spreadsheet that contains data; as you might recall, that\u2019s exactly the row we\u2019re looking for. The problem now is this: how do we figure out which<\/I> row is the last row?<\/P>\n
Here\u2019s how. We start off by using the SpecialCells<\/B> and Activate<\/B> methods to move the cursor to the very last cell in the range and make that cell the active cell. This is where the constant xlCellTypeLastCell gets used; that tells the script to go to the last cell in the range:<\/P>
objRange.SpecialCells(xlCellTypeLastCell).Activate\n<\/PRE>\n
All that puts the cursor in the very last cell in the range; however, we still don\u2019t know the row number of that cell, which means we still don\u2019t know which is the first available row in the spreadsheet. But then, that\u2019s what this code is for:<\/P>
intNewRow = objExcel.ActiveCell.Row + 1\nstrNewCell = “A” &  intNewRow\n<\/PRE>\n
In the first line we get the value of the active cell\u2019s Row<\/B> property and add 1 to it. Why? Well, in our sample spreadsheet the last cell in the range (which is now the active cell) lies in row 16. If we add 1 to that we\u2019ll get row 17, which just happens to be the next available row in the spreadsheet. In the next line we construct a cell address consisting of the letter A<\/I> and the row number; in this example, that\u2019s cell A17<\/B>, which represents the location we should start at when we append data to this spreadsheet.<\/P>\n
Finally we create a new range object consisting of cell A17 and use the Activate method to make A17 the active cell. In turn, that moves the cursor into cell A17, and we\u2019re ready for business. (And, for once, at least, without having to say \u201cUh-oh: I wonder if I really should have done that?\u201d)<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I determine the last row in an Excel spreadsheet? I can\u2019t append data to the file unless I know which row to start with.— TGW Hey, TGW. You know, this is the kind of thing that separates the Scripting Guys from the rest of the world. Most people would do […]<\/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":[48,49,3,5],"class_list":["post-67953","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I determine the last row in an Excel spreadsheet? I can\u2019t append data to the file unless I know which row to start with.— TGW Hey, TGW. You know, this is the kind of thing that separates the Scripting Guys from the rest of the world. Most people would do […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67953","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=67953"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67953\/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=67953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}