{"id":63783,"date":"2007-10-17T22:34:00","date_gmt":"2007-10-17T22:34:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/10\/17\/hey-scripting-guy-how-can-i-insert-new-rows-above-the-last-row-in-an-office-excel-spreadsheet\/"},"modified":"2007-10-17T22:34:00","modified_gmt":"2007-10-17T22:34:00","slug":"hey-scripting-guy-how-can-i-insert-new-rows-above-the-last-row-in-an-office-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-insert-new-rows-above-the-last-row-in-an-office-excel-spreadsheet\/","title":{"rendered":"Hey, Scripting Guy! How Can I Insert New Rows Above the Last Row in an Office Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, <\/H2>\n

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<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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

Note<\/B>. And if you\u2019re wondering why the Scripting Guy who writes this column has all those boxes stacked up in his office<\/I>, well, that has nothing to do with TechEd IT Forum; instead, that\u2019s due to him being too lazy to unpack the last time the Scripting Guys moved to a new building.<\/P>\n

And, yes, the fact that the Scripting Guys have been in this building for a year or so does<\/I> tell you something about how important all his stuff really is, doesn\u2019t it?<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

At any rate, Scripting Guys Jean Ross and Greg Stemp are excited about being able to go to TechEd IT Forum, and we\u2019re looking forward to meeting as many of you as possible (look for us in the Ask the Experts<\/I> 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<\/I> not.) Therefore, we\u2019re sweetening the deal: in addition to our smiling faces (well, OK, Jean\u2019s smiling face and Greg\u2019s grouchy grimace) we\u2019ll be giving away extra-special copies of Dr. Scripto\u2019s Fun Book<\/I> (yes, complete with bonus CD). Even better, you\u2019ll also have one last chance to win your very own Dr. Scripto bobblehead doll<\/B><\/A>. Still not good enough? Well, we might have another trick or two up our sleeves. But you\u2019ll have to swing by the booth in order to find out.<\/P>\n

What if you haven\u2019t 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\u2019t worry; during that week (November 12-16) we\u2019ll do what we can to bring a little bit of TechEd IT Forum to the Script Center.<\/P>\n

And no, that doesn\u2019t<\/I> mean a bunch of boring speakers showing PowerPoint slides. <\/P>\n

Or at least it doesn\u2019t now<\/I>. But we\u2019ll think of something fun between now and then.<\/P>\n

We should note, however, that there is at least one thing that we won\u2019t<\/I> be talking about during that week: a script that can repeatedly insert rows and data above the last row in a spreadsheet. Why aren\u2019t we going to talk about that script during that week? Well, there\u2019s at least one good reason for that: we\u2019re going to talk about that script today instead. Here\u2019s the code:<\/P>

Const xlDown = -4121<\/p>\n
Set 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
Set objRange = objExcel.Range(“A1”)\nobjRange.End(xlDown).Activate<\/p>\n
intRow = objExcel.ActiveCell.Row\nintColumn = objExcel.ActiveCell.Column\nSet objRange = objWorksheet.Cells(intRow, intColumn).EntireRow<\/p>\n
For i = 1 to 10\n    objRange.Insert(xlShiftDown)\nNext<\/p>\n
For i = 1 to 10\n    objExcel.Cells(intRow, 1).Value = i\n    intRow = intRow + 1\nNext<\/p>\n
strFormula = “=SUM(A1:A” & intRow – 1 & “)”\nobjExcel.Cells(intRow, 1).Formula = strFormula\n<\/PRE>\n
OK, so let\u2019s talk about this script and how it works. After all, we promised we would do just that, and the Scripting Guys always<\/I> keep their promises.<\/P>\n
Except, of course, when we don\u2019t. But otherwise we do.<\/P>\n
To begin with, we define a constant named xlDown, assigning it the value -4121; we\u2019ll use this constant to tell the script to shift existing rows down any time we insert a new row. (We\u2019ll 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<\/B> object and set the Visible<\/B> 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:<\/P>
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
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:<\/P>\"Microsoft \n

What we want to do now is insert some blank rows (and then add some data) above<\/I> 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\u2019s a problem; after all, we don\u2019t know which cell is the last cell in column A. (Well, OK, we<\/I> know, because we just saw a picture of the worksheet. But the script doesn\u2019t know.) That\u2019s what these two lines of code are for:<\/P>
Set objRange = objExcel.Range(“A1”)\nobjRange.End(xlDown).Activate\n<\/PRE>\n
In the first line, we\u2019re creating a Range<\/B> object representing cell A1. In the second line, we use the End<\/B> property and the constant xlDown to move the cursor to the last cell in column A, then use the Activate<\/B> 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.<\/P>\n
And yes, that is<\/I> an easy way to find the last cell in a column, provided that the column includes no blank cells. If there are<\/I> blank cells you\u2019ll have to try something else. But that\u2019s a task we\u2019ll have to tackle some other time.<\/P>\n
Oh, what the heck; this bit of code (which we won\u2019t explain today) should do the trick, as long as the very last row in the spreadsheet is empty:<\/P>
Const xlUp = -4162<\/p>\n
Set 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
Set objRange = objExcel.Range(“A65536”)\nobjRange.End(xlUp).Activate\n<\/PRE>\n
So far so good, right? Our next task is to insert some blank rows; in this case, we\u2019re 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\u2019re 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 \u201cgood for you,\u201d we\u2019ll show you a script that does just that at the end of today\u2019s column.)<\/P>\n
As for our<\/I> script, the next thing we do is execute these three lines of code:<\/P>
intRow = objExcel.ActiveCell.Row\nintColumn = objExcel.ActiveCell.Column\nSet objRange = objWorksheet.Cells(intRow, intColumn).EntireRow\n<\/PRE>\n
As you can see, there\u2019s nothing particularly complicated going on here. In line 1, we\u2019re 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<\/B> property to create a range consisting of every cell in row 6.<\/P>\n
Why did we select row 6? One reason and one reason only: when we call the Insert<\/B> method we know we\u2019ll be inserting a new row<\/I> as opposed to inserting a new cell.<\/P>\n
Speaking of inserting new rows, that\u2019s what this block of code is for:<\/P>
For i = 1 to 10\n    objRange.Insert(xlDown)\nNext\n<\/PRE>\n
Again, there\u2019s nothing very fancy here: we\u2019ve simply set up a For Next loop that runs from 1 to 10. Each time we run through the loop we\u2019re 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\u2019ll have a spreadsheet that looks like this:<\/P>\"Microsoft \n

Are there three things you should notice about this worksheet? Funny you should ask; as it turns out, there are<\/I> three things you should notice about this worksheet:<\/P>\n\n\n\n\n\n
\u2022<\/TD>\n\n
We\u2019ve inserted 10 blank rows.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The active cell is still cell A6; that\u2019s because the active cell changes only when we specifically make a new cell the active cell.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The formula that was in cell A6 has been moved down 10 rows, just exactly how we wanted it to be moved.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
All things considered, that was pretty easy. Now, how do we populate these blank rows with data? Well, here\u2019s one simple way to do that:<\/P>
For i = 1 to 10\n    objExcel.Cells(intRow, 1).Value = i\n    intRow = intRow + 1\nNext\n<\/PRE>\n
All we\u2019ve 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:<\/P>
objExcel.Cells(intRow, 1).Value = i\n<\/PRE>\n
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\u2019ve assigned a value to each of the blank rows we inserted into the worksheet.<\/P>\n
At that point, we\u2019re done.<\/P>\n
Wait, check that: at that point we\u2019re almost<\/I> done. We still have one problem: although we\u2019ve moved our formula down to cell A16, the formula still looks like this:<\/P>
=SUM(A1:A5)\n<\/PRE>\n
Unfortunately, when you programmatically insert cells into a worksheet, formulas that refer to those cells don\u2019t automatically update themselves.<\/P>\n
So how do we fix that problem? Well, we couldn\u2019t find a single command (e.g., UpdateAllFormulas) that could do this, so we decided to replace<\/I> the formula rather than update it. That\u2019s what we use this block of code for:<\/P>
strFormula = “=SUM(A1:A” & intRow – 1 & “)”\nobjExcel.Cells(intRow, 1).Formula = strFormula\n<\/PRE>\n
In the first line, we\u2019re constructing an updated formula, this one summing the cells A1 through \u2013 well, through what exactly? In this case, that will be cell A15. Why? Because we\u2019re 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:<\/P>
=SUM(A1:A15)\n<\/PRE>\n
In line 2 we simply assign this new formula to the Formula<\/B> property of cell row 16 (the value of intRow), column 1.<\/P>\n
And then at that<\/I> point we\u2019re done:<\/P>\"Microsoft \n

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. <\/P>\n
And now, as promised, here\u2019s a script that uses a single For Next loop. Just in case you\u2019re interested:<\/P>
Const xlDown = -4121<\/p>\n
Set 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
Set objRange = objExcel.Range(“A1”)\nobjRange.End(xlDown).Activate<\/p>\n
intRow = objExcel.ActiveCell.Row\nintColumn = objExcel.ActiveCell.Column\nSet objRange = objWorksheet.Cells(intRow, intColumn).EntireRow<\/p>\n
For i = 1 to 10\n    objRange.Insert(xlShiftDown)\n    objExcel.Cells(intRow, 1).Value = i\n    intRow = intRow + 1\nNext<\/p>\n
strFormula = “=SUM(A1:A” & intRow – 1 & “)”\nobjExcel.Cells(intRow, 1).Formula = strFormula\n<\/PRE>\n
That should do it, AW; it\u2019s going to have to, because the truck has arrived and it\u2019s time to help load up our boxes. See you in Barcelona!<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. As the Scripting Editor just reminded us, we have to continue working right up until the time we leave for TechEd IT Forum. In that case, see you tomorrow. Sigh \u2026.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
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, […]<\/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,3,5],"class_list":["post-63783","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63783","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=63783"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63783\/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=63783"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63783"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63783"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}