February 21st, 2007

How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?

Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I use a blank row to separate rows of data in a Microsoft Excel spreadsheet?

— AR

SpacerHey, Scripting Guy! AnswerScript Center

Hey, AR. You know, most people believe that the Scripting Guys all think alike, that we always agree on everything. That’s not true, though; instead, like any other team, we disagree from time-to-time. Sure, the team would be best-served by always agreeing with Greg, but for some strange reason we don’t.

Take, for example, the question of which is the best sport in the world. Peter Costantini, our resident bon vivant, would undoubtedly argue that soccer is the best sport in the world. Dean Tsaltas, Canadian-born and bred, would likely vote for hockey. (Of course, “hockey” is Dean’s answer to pretty much any question that comes up.) Jean Ross – well, Jean Ross thinks that car racing is a sport, which means that there’s no reason to consider her opinion, at least not on this question.

As it turns out, though, those three Scripting Guys are all wrong. The best sport in the world, and the truest test of athletic prowess, is this: miniature golf.

Yes, miniature golf, the sport of champions. And no, this has nothing to do with the fact that the Scripting Dad and Scripting Son squared off in miniature golf this past weekend; the Scripting Guy who writes this column has always believed miniature golf was the most grueling and most demanding of all sports. The fact that he shot a 61 – 11 under a par – and beat the Scripting Son by 13 strokes is totally irrelevant.

Although it does prove, once again, that the Scripting Dad is way better than the Scripting Son at anything sports-related.

Weight-lifting? Well, sure, the Scripting Son can bench press more than the Scripting Dad, but weight-lifting doesn’t seem all that sporting-like. Long-distance running? Come on, this is the age of the automobile; no one runs anymore. Throwing a ball harder, farther, and more accurately? Oh, please; name one sport in which throwing a ball hard, far, and accurately matters.

OK, fine. But the Scripting Dad still won the miniature golf showdown. Not only that, but he can write a script that uses a blank row to separate rows of data in a Microsoft Excel spreadsheet:

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

strStartValue = Left(objExcel.Cells(i, 1), 1)

Do Until objExcel.Cells(i, 1) = “” strValue = Left(objExcel.Cells(i, 1), 1) If strValue <> strStartValue Then Set objRange = objExcel.Cells(i,1).EntireRow objRange.Activate objRange.Insert xlShiftDown strStartValue = Left(objExcel.Cells(i + 1, 1), 1) End If i = i + 1 Loop

Before we talk about how this script works we need to explain what AR’s spreadsheet looks like. AR has a spreadsheet that looks something like this:

Microsoft Excel


As you can see, in this spreadsheet there are five different users (listed in column A):

A Person

B Person

C Person

D Person

E Person

The spreadsheet contains one or more rows of data for each of these users; for example, user A Person has data listed in the first three rows. What AR would like to do is insert a blank row between the data for A Person and the data for B Person, eventually ending up with a spreadsheet that looks like this:

Microsoft Excel


But how in the world are we supposed to do that?

Well, to begin with, we create a constant named xlShiftDown and set the value to -4121; we’ll use this constant to tell Excel to shift cells down any time we insert a new, blank row. (If we were working with columns, we could use the constant xlShiftRight, with a value of -4161, to move cells to the right.) We then use this block of code to create a running instance of Excel that we can see onscreen, and to open the file C:\Scripts\Test.xls:

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

Now comes the fun part. To begin with, we assign the value 1 to a counter variable named i; we’ll use this variable to keep track of our position in the spreadsheet. We then use this line of code to grab the first character in cell 1,1 and store it in a variable named strStartValue:

strStartValue = Left(objExcel.Cells(i, 1), 1)

Note. In case you’re wondering, objExcel.Cells(i, 1) gives us the value of cell A1 (row 1, column 1). We then use the Left function to grab just the first character in that value.

The tricky part here is knowing when to insert a blank row; after all, we want to insert a blank row only when we’re done with one user’s data and are moving on to another user’s data. (Needless to say, this script isn’t going to work very well unless the spreadsheet has been sorted by user name. For a programmatic way to sort data take a look at this Office Space article.) How will we figure out when we’re finished with the data for our first user, A Person? Well, we’re going to do that by storing the user data found in the first row in strStartValue, then looking one row ahead to see if the data in that row matches. If it does, then we know we’re still working with A Person. If it doesn’t, that means we’re now working with a new user.

Don’t worry; that should make a little more sense as we walk through the script.

To get things rolling, we set up a Do Until loop designed to loop until we encounter a blank cell in column A:

Do Until objExcel.Cells(i, 1) = “”

Inside that loop, the first thing we do is grab the initial character in the current row and store it in a variable named strValue:

strValue = Left(objExcel.Cells(i, 1), 1)

We now have the first character in cell A1 stored in two different variables: strStartValue and strValue. Our next step is to compare these two values and see if they are different:

If strValue <> strStartValue Then

The first time through the loop the values are not different; both strStartValue and strValue are equal to A. Therefore, we skip the If Then statement, increment the value of i by 1, then loop around and repeat the process with the second row in the spreadsheet. The important thing to note here is that we did not change the value of strStartValue. That variable still holds the letter A, the first character in cell A1. Because we incremented the value of i by 1, however, strValue will now be equal to the first character from cell A2.

See how that works? Good. Now let’s move on.

Sooner or later we’re going to find a discrepancy between strStartValue and strValue; notice that the first character in cell A4 is a B. What happens when strStartValue and strValue no longer match? This happens:

Set objRange = objExcel.Cells(i,1).EntireRow
objRange.Activate
objRange.Insert xlShiftDown
strStartValue = Left(objExcel.Cells(i + 1, 1), 1)

In the first line of code we’re creating a Range object that encompasses all of row 4; we do that by specifying cell A4 (objExcel.Cells(i, 1) and then referencing the EntireRow property. We then use the Activate method to make this the active row in the spreadsheet:

objRange.Activate

Basically what we’ve done here is moved the cursor down to cell A4 and then selected the entire row. With the row selected we use the Insert method to insert a blank row, using the parameter xlShiftDown to shift the existing cells down. What does that mean? That means that the data currently in row 4 will be shifted down to row 5, and row 4 will become a blank row.

Which is exactly what we wanted to happen.

We then use this line of code to grab the first character in row 5 (i + 1) and store that data in strStartValue:

strStartValue = Left(objExcel.Cells(i + 1, 1), 1)

That makes strStartValue equal to B.

We then increment the value of i by 1, loop around, and start the process all over again. Granted, it’s a bit confusing. But it works. Give it a try and you’ll see.

Incidentally, the more we think about it the more we’re wondering if miniature golf really is the best sport in the world. For one thing, it cost the Scripting Dad $16 for he and the Scripting Son to play. For another, what did the Scripting Dad get for winning? You got it: nothing.

Now, compare that to, oh, say, the 2007 Winter Scripting Games. How much will cost you to enter the Scripting Games? Zilch; the Scripting Games are always free to everyone. And what do you get if you win? Well, how about a Dr. Scripto Bobblehead doll or a copy of the book Windows PowerShell in Action? Not bad, huh? You know, come to think of it, maybe the Winter Scripting Games are the best sport in the entire world.

Well, unless it turns out that the Scripting Son actually can write scripts better than the Scripting Dad. In that case, we’ll go back to miniature golf as being the best sport in the world.

Author

0 comments

Discussion are closed.