How Can I Determine the Last Row in an Excel Spreadsheet?

ScriptingGuy1

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.”

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 = 11

Set 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?”)

ScriptingGuy1

Follow