Hey, Scripting Guy! How Can I Insert New Rows Above the Last Row in an Office Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! I have an Office Excel spreadsheet in which the last row in the spreadsheet sums all the rows above it. How can I add new rows (and new data) to this spreadsheet, making sure that the last row always sums up all the rows above it?

— AW

Hey, AW. By the way, please excuse the mess; at the moment we’re sitting around waiting for the truck to arrive so we can ship a whole bunch of stuff to Barcelona for TechEd IT Forum. If you’re wondering why the Scripting Guys have all these boxes stacked up in the hallway, well, now you know.

At any rate, Scripting Guys Jean Ross and Greg Stemp are excited about being able to go to TechEd IT Forum, and we’re looking forward to meeting as many of you as possible (look for us in the Ask the Experts section). And yes, we know: meeting a real, live Scripting Guy might not be the most exciting thing you could do in Barcelona. (We said might not.) Therefore, we’re sweetening the deal: in addition to our smiling faces (well, OK, Jean’s smiling face and Greg’s grouchy grimace) we’ll be giving away extra-special copies of Dr. Scripto’s Fun Book (yes, complete with bonus CD). Even better, you’ll also have one last chance to win your very own Dr. Scripto bobblehead doll. Still not good enough? Well, we might have another trick or two up our sleeves. But you’ll have to swing by the booth in order to find out.

What if you haven’t signed up for TechEd IT Forum just yet? Well, in that case, we have some bad news for you: the event is sold out. (There was a mad rush to register once people heard that Scripting Guy Jean Ross was going to be there in person.) But don’t worry; during that week (November 12-16) we’ll do what we can to bring a little bit of TechEd IT Forum to the Script Center.

And no, that doesn’t mean a bunch of boring speakers showing PowerPoint slides.

Or at least it doesn’t now. But we’ll think of something fun between now and then.

We should note, however, that there is at least one thing that we won’t be talking about during that week: a script that can repeatedly insert rows and data above the last row in a spreadsheet. Why aren’t we going to talk about that script during that week? Well, there’s at least one good reason for that: we’re going to talk about that script today instead. Here’s the code:

Const xlDown = -4121

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objExcel.Range(“A1”)
objRange.End(xlDown).Activate
intRow = objExcel.ActiveCell.Row
intColumn = objExcel.ActiveCell.Column
Set objRange = objWorksheet.Cells(intRow, intColumn).EntireRow
For i = 1 to 10
    objRange.Insert(xlShiftDown)
Next
For i = 1 to 10
    objExcel.Cells(intRow, 1).Value = i
    intRow = intRow + 1
Next
strFormula = “=SUM(A1:A” & intRow – 1 & “)”
objExcel.Cells(intRow, 1).Formula = strFormula

OK, so let’s talk about this script and how it works. After all, we promised we would do just that, and the Scripting Guys always keep their promises.

Except, of course, when we don’t. But otherwise we do.

To begin with, we define a constant named xlDown, assigning it the value -4121; we’ll use this constant to tell the script to shift existing rows down any time we insert a new row. (We’ll also use it to help us find the last cell in column A.) After defining the constant we create an instance of the Excel.Application object and set the Visible property to True; that gives us a running instance of Excel that we can see onscreen. From there we use the following two lines of code to open the file C:\Scripts\Test.xls and then bind to the first worksheet in that file:

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)

And what does this worksheet look like? Well, for starters, it looks like this, with data in cells A1 through A5 and a formula (a formula that sums all the cells above) in cell A6:

What we want to do now is insert some blank rows (and then add some data) above row 6; that way our formula remains the last row in the worksheet. How are we going to do that? Well, to begin with, we need to position the cursor in the last cell in column A. And that’s a problem; after all, we don’t know which cell is the last cell in column A. (Well, OK, we know, because we just saw a picture of the worksheet. But the script doesn’t know.) That’s what these two lines of code are for:

Set objRange = objExcel.Range(“A1”)
objRange.End(xlDown).Activate

In the first line, we’re creating a Range object representing cell A1. In the second line, we use the End property and the constant xlDown to move the cursor to the last cell in column A, then use the Activate method to make this cell the active cell. The net result? The cursor will be positioned in cell A6, the cell that contains our formula.

And yes, that is an easy way to find the last cell in a column, provided that the column includes no blank cells. If there are blank cells you’ll have to try something else. But that’s a task we’ll have to tackle some other time.

Oh, what the heck; this bit of code (which we won’t explain today) should do the trick, as long as the very last row in the spreadsheet is empty:

Const xlUp = -4162

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objExcel.Range(“A65536”)
objRange.End(xlUp).Activate

So far so good, right? Our next task is to insert some blank rows; in this case, we’re going to insert 10 blank rows. Instead of doing this in one fell swoop, we decided to first insert the blank rows and then add data to these blank rows. Why? We thought it was a little easier for everyone to follow. Consequently, we’re going to use two separate For Next loops in our script; if you prefer to combine these two tasks (inserting a blank row and then adding data to that row) into a single For Next loop, all we can say is this: good for you. (And, in addition to saying “good for you,” we’ll show you a script that does just that at the end of today’s column.)

As for our script, the next thing we do is execute these three lines of code:

intRow = objExcel.ActiveCell.Row
intColumn = objExcel.ActiveCell.Column
Set objRange = objWorksheet.Cells(intRow, intColumn).EntireRow

As you can see, there’s nothing particularly complicated going on here. In line 1, we’re simply assigning the row number for the active cell (that will be row 6, because the active cell is cell A6) to a variable named intRow. In line 2, we assign the column number (1) of the active cell to a variable named intColumn. Finally, in line 3, we use the EntireRow property to create a range consisting of every cell in row 6.

Why did we select row 6? One reason and one reason only: when we call the Insert method we know we’ll be inserting a new row as opposed to inserting a new cell.

Speaking of inserting new rows, that’s what this block of code is for:

For i = 1 to 10
    objRange.Insert(xlDown)
Next

Again, there’s nothing very fancy here: we’ve simply set up a For Next loop that runs from 1 to 10. Each time we run through the loop we’re going to use the Insert method to insert a new row, with the constant xlDown telling the script to move any existing rows down. By the time we exit the loop we’ll have a spreadsheet that looks like this:

Are there three things you should notice about this worksheet? Funny you should ask; as it turns out, there are three things you should notice about this worksheet:

All things considered, that was pretty easy. Now, how do we populate these blank rows with data? Well, here’s one simple way to do that:

For i = 1 to 10
    objExcel.Cells(intRow, 1).Value = i
    intRow = intRow + 1
Next

All we’ve done here is set up another For Next loop, a For Next loop that also runs from 1 to 10. (Why? Well, having inserted 10 blank rows, we now want to add data to each of these rows). As we pointed out a moment ago, the cursor is still positioned in cell A6, and the variable intRow (which we used to store the row number of the active cell) is still equal to 6. Therefore, we can assign a value to the first blank cell simply by using code like this:

objExcel.Cells(intRow, 1).Value = i

This simply sets the value of cell row 6 (the variable intRow), column 1 to our counter variable i. We increment the value of intRow by 1 (making the value 7), then loop around and assign a value to cell row 7, column 1. This continues until we’ve assigned a value to each of the blank rows we inserted into the worksheet.

At that point, we’re done.

Wait, check that: at that point we’re almost done. We still have one problem: although we’ve moved our formula down to cell A16, the formula still looks like this:

=SUM(A1:A5)

Unfortunately, when you programmatically insert cells into a worksheet, formulas that refer to those cells don’t automatically update themselves.

So how do we fix that problem? Well, we couldn’t find a single command (e.g., UpdateAllFormulas) that could do this, so we decided to replace the formula rather than update it. That’s what we use this block of code for:

strFormula = “=SUM(A1:A” & intRow – 1 & “)”
objExcel.Cells(intRow, 1).Formula = strFormula

In the first line, we’re constructing an updated formula, this one summing the cells A1 through – well, through what exactly? In this case, that will be cell A15. Why? Because we’re taking the value of intRow (which is currently 16) and subtracting 1 from it; that leaves us with row 15. In other words, the value of our string variable strFormula will end up being this:

=SUM(A1:A15)

In line 2 we simply assign this new formula to the Formula property of cell row 16 (the value of intRow), column 1.

And then at that point we’re done:

The best part, of course, is that the next time you append data to this worksheet all the fun will start in row A16, the row that now contains the formula.

And now, as promised, here’s a script that uses a single For Next loop. Just in case you’re interested:

Const xlDown = -4121

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objExcel.Range(“A1”)
objRange.End(xlDown).Activate
intRow = objExcel.ActiveCell.Row
intColumn = objExcel.ActiveCell.Column
Set objRange = objWorksheet.Cells(intRow, intColumn).EntireRow
For i = 1 to 10
    objRange.Insert(xlShiftDown)
    objExcel.Cells(intRow, 1).Value = i
    intRow = intRow + 1
Next
strFormula = “=SUM(A1:A” & intRow – 1 & “)”
objExcel.Cells(intRow, 1).Formula = strFormula

That should do it, AW; it’s going to have to, because the truck has arrived and it’s time to help load up our boxes. See you in Barcelona!

ScriptingGuy1

Follow