Hey, Scripting Guy! How can I determine the last row in an Excel spreadsheet? I can’t 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 the same thing you’re doing: looking at their spreadsheet and thinking, “OK, so how do I know where to start? After all, I want to make sure I do this right.” That’s not the kind of question you’ll 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: “Uh-oh: I wonder if I really should have done that.”
Note. And, yes, we are 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. And, no, he wasn’t particularly upset. After all, for that Scripting Guy it was just another day at the office. |
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’s just that we don’t bother to do things the right way. But you don’t 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:
Const xlCellTypeLastCell = 11Set objExcel = CreateObject(“Excel.Application”) objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”) Set objWorksheet = objWorkbook.Worksheets(1) objWorksheet.Activate
Set objRange = objWorksheet.UsedRange objRange.SpecialCells(xlCellTypeLastCell).Activate
intNewRow = objExcel.ActiveCell.Row + 1 strNewCell = “A” & intNewRow
objExcel.Range(strNewCell).Activate
Before we explain this all let’s show you what our sample spreadsheet looks like:
Now, we didn’t show you this sample spreadsheet because we’re so proud of it. (Although, come to think of it, it is pretty nice, isn’t it?) Instead we wanted to point out that it’s a spreadsheet that includes some blank rows. Is that important? It can be. For example, it’s possible to mimic the use of the Ctrl+Down Arrow using a script. That can help you determine the last row in a spreadsheet … provided, of course, that you don’t have any blank rows. In our sample, spreadsheet, however, a Ctrl+Down Arrow script won’t work: that’s 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’s why we didn’t use a script that mimicked Ctrl+Down Arrow.
Incidentally, that’s also why we didn’t just check to see if the value of cell A-whatever was equal to nothing and call it good. In a spreadsheet like this one, some rows simply have no value in column A.
So what does our script do? Well, it starts out by defining a constant named xlCellTypeLastCell and setting the value to 11; we’ll 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 object, open the file C:\Scripts\test.xls, and then create an object reference to Sheet 1:
Set objExcel = CreateObject(“Excel.Application”) objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”) Set objWorksheet = objWorkbook.Worksheets(1) objWorksheet.Activate
Now it’s time to roll up our sleeves and get to work. The first thing we do is issue this command:
Set objRange = objWorksheet.UsedRange
Why? We’re glad you asked that. The UsedRange 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’s the used range going to be? It’s going to be a range that starts in cell A1, ends in cell Z37, and includes all the cells in between.
The key here is that the used range includes the very last row in the spreadsheet that contains data; as you might recall, that’s exactly the row we’re looking for. The problem now is this: how do we figure out which row is the last row?
Here’s how. We start off by using the SpecialCells and Activate 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:
objRange.SpecialCells(xlCellTypeLastCell).Activate
All that puts the cursor in the very last cell in the range; however, we still don’t know the row number of that cell, which means we still don’t know which is the first available row in the spreadsheet. But then, that’s what this code is for:
intNewRow = objExcel.ActiveCell.Row + 1 strNewCell = “A” & intNewRow
In the first line we get the value of the active cell’s Row 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’ll 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 and the row number; in this example, that’s cell A17, which represents the location we should start at when we append data to this spreadsheet.
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’re ready for business. (And, for once, at least, without having to say “Uh-oh: I wonder if I really should have done that?”)
0 comments