Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\nFrom there we then assign the value 1 to a pair of counter variables, i and j. We\u2019re going to use i to keep track of our current location in the worksheet, and we\u2019re going to use j to let us know when it\u2019s time to insert a row. With any luck, exactly how<\/I> we\u2019re going to accomplish both of those feats will become clear in a minute or two.<\/P>\n
For educational purposes, we\u2019re 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\u2019re going to assume that we\u2019ve reached the end of the worksheet. And, just like we said, we\u2019re using the counter variable i to keep track of our location in the worksheet:<\/P>
Do Until objWorkSheet.Cells (i, 1) = “”\n<\/PRE>\nBecause i is equal to 1, that means that, the first time through the loop, we\u2019ll be looking at the value of cell row 1, column 1. That brings us to this line of code:<\/P>
If j = 241 Then\n<\/PRE>\nWhat\u2019s 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\u2019t 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.<\/P>\n
Ah, but suppose that counter variable j is<\/I> equal to 241. If that\u2019s the case, that means that we just passed row number 240; in turn, that means it\u2019s time to enter a blank row:<\/P>Set objRange = objWorksheet.Cells(i, 1).EntireRow\nobjRange.Insert(xlShiftDown)\n<\/PRE>\nWhat are we doing here? Well, for starters, we\u2019re creating an instance of Excel\u2019s Range<\/B> 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)<\/B> gives us the current cell in column A, while the EntireRow<\/B> property gives us all the other cells in the row.) We then call the Insert<\/B> 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.<\/P>\n
Now, if all we wanted to do was periodically insert blank rows into our spreadsheet then we\u2019d be done; in fact, if all you do<\/I> want to do is periodically insert blank rows into your spreadsheet here\u2019s a script that does just that:<\/P>Const xlShiftDown = -4121<\/p>\nSet objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
i = 1\nj = 1<\/p>\n
Do Until objWorkSheet.Cells (i, 1) = “”\n If j = 241 Then\n Set objRange = objWorksheet.Cells(i, 1).EntireRow\n objRange.Insert(xlShiftDown)\n j = 0\n End If\n j = j + 1\n i = i + 1\nLoop\n<\/PRE>\n
Of course, we don\u2019t want to just insert blank rows in our worksheet; we want to stick a formula for calculating averages into those blank rows. And here\u2019s how we do that<\/I>:<\/P>strFormula = “=AVERAGE(A” & i – 1 & “:A” & i – 240 & “)”\nobjWorksheet.Cells(i, 1) = strFormula\n<\/PRE>\nWe 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:<\/P>
=AVERAGE(A240:A1)\n<\/PRE>\nBelieve it or not, our first line of code constructs that very formula. To do so we combine these elements:<\/P>\n
\n\n\n| \u2022<\/TD>\n | \n The string value =AVERAGE(A<\/B>.<\/P><\/TD><\/TR>\n\n| \u2022<\/TD>\n | \n The current row minus 1 (i \u2013 1<\/B>). Why i minus 1? Well, currently we\u2019re 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\u2019t, we\u2019ll 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.<\/P><\/TD><\/TR>\n\n| \u2022<\/TD>\n | \n The string value :A<\/B><\/P><\/TD><\/TR>\n\n| \u2022<\/TD>\n | \n 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.<\/P><\/TD><\/TR>\n | \n| \u2022<\/TD>\n | \n The string value )<\/B>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n Add them all together and you get this:<\/P> =AVERAGE(A240:A1)\n<\/PRE>\nAnd yes, that is<\/I> a little backwards; you\u2019d expect the formula to start with A1 and end with A240:<\/P>=AVERAGE(A1:A240)\n<\/PRE>\nBut that\u2019s all right. We thought this \u201cbackwards\u201d method was a little easier to follow, and Excel doesn\u2019t care: go ahead and enter the formula like this and Excel will automatically correct it for you.<\/P>\n Good old Excel.<\/P>\n After writing the formula to the current cell, we next reset the value of j to 0. Why? Because it\u2019s now time to start counting off the next set of 240 rows. To that<\/I> end, we increment both our counter variables by 1, then loop around and try this with the next row in the worksheet:<\/P>j = j + 1\ni = i + 1\n<\/PRE>\nIncidentally, 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\u2019s fine. To do that, first change the If Then statement to check to see if j is equal to 26<\/B> (one more than the desired interval):<\/P>If j = 26 Then\n<\/PRE>\nAnd then make sure you subtract the interval value (25<\/B>) when putting together the formula:<\/P>strFormula = “=AVERAGE(A” & i – 1 & “:A” & i – 25 & “)”\n<\/PRE>\nPretty easy, huh?<\/P>\n Oh, good point; what is<\/I> this line of code for:<\/P>objWorksheet.Cells(i, 1).Interior.ColorIndex = 44\n<\/PRE>\nThis 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\u2019ll see what we mean.)<\/P>\n After we exit the loop we then check to see whether or not j is equal to 0; if it is, then we\u2019re done; we\u2019ve averaged all the rows in the last series. If j is not<\/I> equal to 0 (for example, if j is equal to 113) then we have a few rows (in this case, 113) that haven\u2019t been averaged. We use this block of code to take care of that little problem:<\/P>Set objRange = objWorksheet.Cells(i, 1).EntireRow\nobjRange.Insert(xlShiftDown)<\/p>\nstrFormula = “=AVERAGE(A” & i – 1 & “:A” & i – j + 1 & “)”\nobjWorksheet.Cells(i, 1) = strFormula\nobjWorksheet.Cells(i, 1).Interior.ColorIndex = 44\n<\/PRE>\n And that \u2013 at long last \u2013 is that.<\/P>\n What\u2019s that? You have one last question? Now that he\u2019s 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\u2019ll soon be traveling to new locations and new airports, locations and airports even more<\/I> exotic than Walla Walla, WA or Moscow, ID? Let\u2019s put it this way: we sure hope not.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/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":[710,48,49,3,5],"class_list":["post-63843","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":" 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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63843","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=63843"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63843\/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=63843"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63843"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63843"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} | | | |
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/H2>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \n