Learn about Windows PowerShell
Hey, Scripting Guy! I have some extremely large Office Excel worksheets, and I need to insert a blank row every 240 lines; in that new row I then need to insert a formula that calculates the average of the preceding 240 rows. As you might expect, this is extremely tedious, and I have a lot of these worksheets. How can I write a script that inserts rows and formulas for me?-- BS
Hey, BS. You know, we’re sure that a lot of people wonder what the Scripting Guys do in their spare time. Well, we can’t speak for the other Scripting Guys, but we can tell you that the Scripting Guy who writes this column has an asbolutely fascinating hobby: for reasons even he doesn’t fully understand, he spends most of his spare time visiting obscure, out-of-the-way airports that nobody else ever goes to.
For example, earlier this year, the Scripting Guy who writes this column was excited about the chance to visit the airport in Walla Walla, WA. As it turned out, however, he never got that chance: after driving the 45 miles from the Tri-Cities to Walla Walla, he discovered that – thanks to modern air travel, which primarily involves airplanes that don’t actually do any traveling – the flight he was supposed to meet had been cancelled. Instead, he ended up driving back to the Tri-Cities, hung around the Tri-Cities airport for awhile (after the rescheduled flight was delayed, then delayed again), and then drove back to Walla Walla.
And all just to watch the Scripting Son play baseball.
And then, just last Friday, this same Scripting Guy (and that same Scripting Son) got to visit the Pullman-Moscow Regional Airport in Pullman, WA. (All part of the fun and excitement of Vandal Preview Day.) Is the Pullman-Moscow Regional Airport a small airport? You might say that: after all, the entire terminal is housed in a single room, a room that includes a rental car counter, a ticket desk, and the baggage claim. The Scripting Guy who writes this column and his son arrived about 10:45 PM for a flight scheduled to land at 11:10 PM. For the next 20 minutes or so, they were the only people in the entire airport; there was not a soul to be seen anywhere. Including anyone who, we assume, was supposed to be working at the airport.
So was that kind of spooky? Not really; there were plenty of things to do at the airport. For example, there were several decks of cards lying around, so the Scripting Son and his father played a spirited game of War. (A game won by the Scripting Dad, of course.) In addition there was a self-service bookstore in the terminal, a “bookstore” that was really just a couple bookcases filled with books along with a slot for depositing your money. The Scripting Guy who writes this column actually bought four books (nice ones) for $20. Now that was cool: try buying even one book for $20 at your typical big city airport.
At any rate, between the self-service bookstore, the decks of cards, and the two vending machines (not to mention the free parking), the Pullman-Moscow Airport had pretty much anything a traveler (or someone assigned to pick up a traveler) could ever need. Well, except maybe for one thing: it didn’t have a script that could periodically insert blank rows (and formulas) into an Excel spreadsheet. But that’s OK; after all, the Scripting Guy who writes this column would never leave home without a script like that:
Const xlShiftDown = -4121 Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls") Set objWorksheet = objWorkbook.Worksheets(1) i = 1 j = 1 Do Until objWorkSheet.Cells (i, 1) = "" If j = 241 Then Set objRange = objWorksheet.Cells(i, 1).EntireRow objRange.Insert(xlShiftDown) strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - 240 & ")" objWorksheet.Cells(i, 1) = strFormula objWorksheet.Cells(i, 1).Interior.ColorIndex = 44 j = 0 End If j = j + 1 i = i + 1 Loop If j <> 0 Then Set objRange = objWorksheet.Cells(i, 1).EntireRow objRange.Insert(xlShiftDown) strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - j + 1 & ")" objWorksheet.Cells(i, 1) = strFormula objWorksheet.Cells(i, 1).Interior.ColorIndex = 44 End If
So how does this script work? We were afraid you were going to ask that question; this is definitely a complicated-looking batch of code, isn’t it? But let’s see if we can sit down and figure this out together.
As you can see, we start out by defining a constant named xlShiftDown and setting the value to -4121; we’ll use this constant to tell Excel which way to shift the existing cells each time we insert a new row.
Scripting GuysTrivia. By amazing coincidence, -4121 is both the Scripting Editor’s batting average for the past softball season and her score on the SAT test. What do you suppose the odds are of that?
After we define the constant, we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Excel that we can see on screen. We then use the following two lines of code to open the file C:\Scripts\Test.xls and bind us to the first worksheet in that file:
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls") Set objWorksheet = objWorkbook.Worksheets(1)
From there we then assign the value 1 to a pair of counter variables, i and j. We’re going to use i to keep track of our current location in the worksheet, and we’re going to use j to let us know when it’s time to insert a row. With any luck, exactly how we’re going to accomplish both of those feats will become clear in a minute or two.
For educational purposes, we’re assuming that we have a very simple spreadsheet, one with a bunch of numbers in column A, and with no blank rows between those numbers. With that in mind, our next step is to set up a Do Until loop that runs until we encounter a blank cell in column A; when that happens, we’re going to assume that we’ve reached the end of the worksheet. And, just like we said, we’re using the counter variable i to keep track of our location in the worksheet:
Do Until objWorkSheet.Cells (i, 1) = ""
Because i is equal to 1, that means that, the first time through the loop, we’ll be looking at the value of cell row 1, column 1. That brings us to this line of code:
If j = 241 Then
What’s this for? Well, BS wants to insert a new row every 240 rows. We use the counter variable j to keep track of the rows as we work our way down the spreadsheet. If j is not equal to 241, that means that we haven’t counted off 240 rows yet. Therefore, we simply skip the If-Then block, increment the values of both counter variables by 1, then repeat this process with the next row in the worksheet.
Ah, but suppose that counter variable j is equal to 241. If that’s the case, that means that we just passed row number 240; in turn, that means it’s time to enter a blank row:
Set objRange = objWorksheet.Cells(i, 1).EntireRow objRange.Insert(xlShiftDown)
What are we doing here? Well, for starters, we’re creating an instance of Excel’s Range object, a Range object that encompasses the current row. (How do we know that this Range object encompasses the current row? Well, objWorksheet.Cells(i, 1) gives us the current cell in column A, while the EntireRow property gives us all the other cells in the row.) We then call the Insert method to insert a new row, using the constant xlShiftDown to tell the script to insert the new row by moving existing cells down a row.
Now, if all we wanted to do was periodically insert blank rows into our spreadsheet then we’d be done; in fact, if all you do want to do is periodically insert blank rows into your spreadsheet here’s a script that does just that:
Const xlShiftDown = -4121 Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls") Set objWorksheet = objWorkbook.Worksheets(1) i = 1 j = 1 Do Until objWorkSheet.Cells (i, 1) = "" If j = 241 Then Set objRange = objWorksheet.Cells(i, 1).EntireRow objRange.Insert(xlShiftDown) j = 0 End If j = j + 1 i = i + 1 Loop
Of course, we don’t want to just insert blank rows in our worksheet; we want to stick a formula for calculating averages into those blank rows. And here’s how we do that:
strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - 240 & ")" objWorksheet.Cells(i, 1) = strFormula
We thought you might have a question about that first line of code. When you insert a formula into a cell, you need to use syntax similar to this:
=AVERAGE(A240:A1)
Believe it or not, our first line of code constructs that very formula. To do so we combine these elements:
The string value =AVERAGE(A.
The current row minus 1 (i – 1). Why i minus 1? Well, currently we’re on row 241 and we only want our formula to average the rows from 1 to 240. Thus we need to subtract 1 from the current row number. If we don’t, we’ll get a whole host of circular reference errors. Um, not that the Scripting Guy who writes this column got a whole host of circular reference errors on his first try at writing this script, mind you.
The string value :A
The starting row for our set of 240 rows. How do we calculate that? By subtracting 240 from the counter variable i. If we subtract 240 from 241 we get 1, which is good; after all, the first row in our dataset is row 1.
The string value ).
Add them all together and you get this:
And yes, that is a little backwards; you’d expect the formula to start with A1 and end with A240:
=AVERAGE(A1:A240)
But that’s all right. We thought this “backwards” method was a little easier to follow, and Excel doesn’t care: go ahead and enter the formula like this and Excel will automatically correct it for you.
Good old Excel.
After writing the formula to the current cell, we next reset the value of j to 0. Why? Because it’s now time to start counting off the next set of 240 rows. To that end, we increment both our counter variables by 1, then loop around and try this with the next row in the worksheet:
j = j + 1 i = i + 1
Incidentally, you can easily modify this script to insert blank rows at an interval other than 240. For example, suppose you want to insert a blank row every 25 rows. That’s fine. To do that, first change the If Then statement to check to see if j is equal to 26 (one more than the desired interval):
If j = 26 Then
And then make sure you subtract the interval value (25) when putting together the formula:
strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - 25 & ")"
Pretty easy, huh?
Oh, good point; what is this line of code for:
objWorksheet.Cells(i, 1).Interior.ColorIndex = 44
This is optional, but to make it a bit easier to distinguish formula cells from the other cells in column A we use this line of code to change the background color of the formula cells to orange. (Give it a try and you’ll see what we mean.)
After we exit the loop we then check to see whether or not j is equal to 0; if it is, then we’re done; we’ve averaged all the rows in the last series. If j is not equal to 0 (for example, if j is equal to 113) then we have a few rows (in this case, 113) that haven’t been averaged. We use this block of code to take care of that little problem:
Set objRange = objWorksheet.Cells(i, 1).EntireRow objRange.Insert(xlShiftDown) strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - j + 1 & ")" objWorksheet.Cells(i, 1) = strFormula objWorksheet.Cells(i, 1).Interior.ColorIndex = 44
And that – at long last – is that.
What’s that? You have one last question? Now that he’s taken care of the insert-a-blank-row-and-formula-into-a-spreadsheet problem, you want to know if the Scripting Guy who writes this column has been left with a whole bunch of time on his hands? In turn, does that mean he’ll soon be traveling to new locations and new airports, locations and airports even more exotic than Walla Walla, WA or Moscow, ID? Let’s put it this way: we sure hope not.
thanks