{"id":65473,"date":"2007-02-21T00:58:00","date_gmt":"2007-02-21T00:58:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/02\/21\/how-can-i-use-a-blank-row-to-separate-data-in-an-excel-spreadsheet\/"},"modified":"2007-02-21T00:58:00","modified_gmt":"2007-02-21T00:58:00","slug":"how-can-i-use-a-blank-row-to-separate-data-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-use-a-blank-row-to-separate-data-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, <\/H2>\n

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

— AR<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, AR. You know, most people believe that the Scripting Guys all think alike, that we always agree on everything. That\u2019s 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\u2019t.<\/P>\n

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, \u201chockey\u201d is Dean\u2019s answer to pretty much any question that comes up.) Jean Ross \u2013 well, Jean Ross thinks that car racing<\/I> is a sport, which means that there\u2019s no reason to consider her opinion, at least not on this question. <\/P>\n

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.<\/P>\n

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 \u2013 11 under a par \u2013 and beat the Scripting Son by 13 strokes is totally irrelevant.<\/P>\n

Although it does prove, once again, that the Scripting Dad is way<\/I> better than the Scripting Son at anything sports-related.<\/P>\n

Weight-lifting? Well, sure, the Scripting Son can bench press more than the Scripting Dad, but weight-lifting doesn\u2019t 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.<\/P>\n

OK, fine. But the Scripting Dad still<\/I> 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:<\/P>

Const xlShiftDown = -4121<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
i = 1<\/p>\n
strStartValue = Left(objExcel.Cells(i, 1), 1)<\/p>\n
Do Until objExcel.Cells(i, 1) = “”\n    strValue = Left(objExcel.Cells(i, 1), 1)\n    If strValue <> strStartValue Then\n        Set objRange = objExcel.Cells(i,1).EntireRow\n        objRange.Activate\n        objRange.Insert xlShiftDown\n        strStartValue = Left(objExcel.Cells(i + 1, 1), 1)\n    End If\n    i = i + 1\nLoop\n<\/PRE>\n
Before we talk about how this script works we need to explain what AR\u2019s spreadsheet looks like. AR has a spreadsheet that looks something like this:<\/P>\"Microsoft \n

As you can see, in this spreadsheet there are five different users (listed in column A):<\/P>\n\n\n\n\n\n\n\n
\u2022<\/TD>\n\n
A Person<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
B Person<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
C Person<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
D Person<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
E Person<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
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:<\/P>\"Microsoft \n

But how in the world are we supposed to do that?<\/P>\n
Well, to begin with, we create a constant named xlShiftDown and set the value to -4121; we\u2019ll 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:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
Now comes the fun part. To begin with, we assign the value 1 to a counter variable named i<\/I>; we\u2019ll 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:<\/P>
strStartValue = Left(objExcel.Cells(i, 1), 1)\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. In case you\u2019re wondering, objExcel.Cells(i, 1) <\/B>gives us the value of cell A1 (row 1, column 1). We then use the Left<\/B> function to grab just the first character in that value.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
The tricky part here is knowing when to insert a blank row; after all, we want to insert a blank row only when we\u2019re done with one user\u2019s data and are moving on to another user\u2019s data. (Needless to say, this script isn\u2019t 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<\/B><\/A>.) How will we figure out when we\u2019re finished with the data for our first user, A Person? Well, we\u2019re 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<\/I> row matches. If it does, then we know we\u2019re still working with A Person. If it doesn\u2019t, that means we\u2019re now working with a new user.<\/P>\n
Don\u2019t worry; that should make a little more sense as we walk through the script.<\/P>\n
To get things rolling, we set up a Do Until loop designed to loop until we encounter a blank cell in column A:<\/P>
Do Until objExcel.Cells(i, 1) = “”\n<\/PRE>\n
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:<\/P>
strValue = Left(objExcel.Cells(i, 1), 1)\n<\/PRE>\n
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:<\/P>
If strValue <> strStartValue Then\n<\/PRE>\n
The first time through the loop the values are not<\/I> different; both strStartValue and strValue are equal to A<\/I>. 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<\/I> change the value of strStartValue. That variable still holds the letter A<\/I>, 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. <\/P>\n
See how that works? Good. Now let\u2019s move on.<\/P>\n
Sooner or later we\u2019re going to find a discrepancy between strStartValue and strValue; notice that the first character in cell A4 is a B<\/I>. What happens when strStartValue and strValue no longer match? This happens:<\/P>
Set objRange = objExcel.Cells(i,1).EntireRow\nobjRange.Activate\nobjRange.Insert xlShiftDown\nstrStartValue = Left(objExcel.Cells(i + 1, 1), 1)\n<\/PRE>\n
In the first line of code we\u2019re creating a Range object that encompasses all of row 4; we do that by specifying cell A4 (objExcel.Cells(i, 1)<\/B> and then referencing the EntireRow<\/B> property. We then use the Activate<\/B> method to make this the active row in the spreadsheet:<\/P>
objRange.Activate\n<\/PRE>\n
Basically what we\u2019ve done here is moved the cursor down to cell A4 and then selected the entire row. With the row selected we use the Insert<\/B> 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.<\/P>\n
Which is exactly what we wanted to happen.<\/P>\n
We then use this line of code to grab the first character in row 5<\/I> (i + 1) and store that data in strStartValue:<\/P>
strStartValue = Left(objExcel.Cells(i + 1, 1), 1)\n<\/PRE>\n
That makes strStartValue equal to B<\/I>.<\/P>\n
We then increment the value of i by 1, loop around, and start the process all over again. Granted, it\u2019s a bit confusing. But it works. Give it a try and you\u2019ll see.<\/P>\n
Incidentally, the more we think about it the more we\u2019re wondering if miniature golf really is<\/I> 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. <\/P>\n
Now, compare that to, oh, say, the 2007 Winter Scripting Games<\/B><\/A>. 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<\/B><\/A> or a copy of the book Windows PowerShell in Action<\/B><\/A>? Not bad, huh? You know, come to think of it, maybe the Winter Scripting Games<\/I> are the best sport in the entire world.<\/P>\n
Well, unless it turns out that the Scripting Son actually can<\/I> write scripts better than the Scripting Dad. In that case, we\u2019ll go back to miniature golf as being the best sport in the world.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I use a blank row to separate rows of data in a Microsoft Excel spreadsheet?— AR Hey, AR. You know, most people believe that the Scripting Guys all think alike, that we always agree on everything. That\u2019s not true, though; instead, like any other team, we disagree from time-to-time. Sure, […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[48,49,3,5],"class_list":["post-65473","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I use a blank row to separate rows of data in a Microsoft Excel spreadsheet?— AR Hey, AR. You know, most people believe that the Scripting Guys all think alike, that we always agree on everything. That\u2019s not true, though; instead, like any other team, we disagree from time-to-time. Sure, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65473","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=65473"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65473\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=65473"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65473"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65473"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}