How to add multiple rows in excel 2007 code#

What does all that mean? That means, the first time through, cell row 1, column 1 (A1) will end up with a 1 in it. (Hey, just like we said we were going to do!) The first time through the loop i is equal to 1 as it turns out, the first value we grab from the array is also equal to 1.

How to add multiple rows in excel 2007 code#

If you look closely at the code you can see that we use the counter variable i to represent the row. Inside that loop we use this line of code to set the value of the cell in column 1 of the current row to the value we grabbed from the array: objExcel.Cells(i, 1).Value = intValue To do that we simply set up a For Each loop that loops through each value in the array arrValues. With Excel up and running, and visible onscreen, we then set the value of a counter variable named i – a variable we’ll use to keep track of the current row in the spreadsheet – to 1: i = 1Īs soon as that’s done we’re ready to start adding data to our spreadsheet. We went this route simply because it helps us keep the code as short and sweet and possible.Īfter adding values to our array we then use the following block of code to create an instance of the Execl.Application object, make that instance visible, and then give that instance a new workbook: Set objExcel = CreateObject('Excel.Application') Do you need to put all of your values into an array before adding them to your spreadsheet? Probably not. Why do we do this? That’s easy: we need a bunch of values to add to the spreadsheet, and this seemed as good a way as any to do that. As you can see, we start out simply by assigning a bunch of numbers to an array named arrValues: arrValues = Array(1,5,7,9,11,13,15,17) To make a fancier, multi-column spreadsheet all you need to do is take the basic recipe shown here and extend it a little. That might not be especially realistic, but it should make it easier for you to follow what we’re doing. ObjExcel.Cells(i, 1).Formula = '=SUM(A1:A' & i - 1 & ')'īefore we go much further we should point out that we’re working with a very simple, one-column spreadsheet here. Set objExcel = CreateObject('Excel.Application') But what the heck we’ll humor the Scripting Editor and answer the question anyway: arrValues = Array(1,5,7,9,11,13,15,17) Well, just between us, we don’t think she is, either. After all, the law of averages suggests that sooner or later she’s bound to be right about something. (Hey, the season will be over sometime in April.) On the other hand, maybe the Scripting Editor is right. To tell you the truth, we didn’t think we were kidding we thought we were serious. It seems that the Scripting Editor – who is becoming more and more of a killjoy by the moment (as if that was even possible) – has informed us that we were kidding when we suggested that we weren’t going to answer any more scripting questions until after the college basketball season was over. Tonight is the third and final day of the tournament, which means the Scripting Guy who writes this column is absolutely swamped trying to figure out what they should have for dinner this time around.Īnyway if you could get back to us, say when the college basketball season is over, that would really help a lot. Yesterday they went over early and had pulled-pork barbecue sandwiches. Because they like watching both games, including the game that doesn’t involve the Huskies, they went over early and ate dinner beforehand: deli sandwiches (with tons of meat). Sunday the Scripting Guy who writes this column and his Scripting Son went to the tournament’s opening day. Could we ask you a favor: is there any chance that you could write back later with this question? As you may or may not know, the University of Washington men’s basketball team is hosting a four-team, round-robin tournament this week. Hey, Scripting Guy! How can I add a bunch of rows to an Excel spreadsheet and then stick a total row at the bottom?