{"id":70053,"date":"2005-04-11T15:33:00","date_gmt":"2005-04-11T15:33:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/04\/11\/how-can-i-insert-a-column-into-a-spreadsheet\/"},"modified":"2005-04-11T15:33:00","modified_gmt":"2005-04-11T15:33:00","slug":"how-can-i-insert-a-column-into-a-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-insert-a-column-into-a-spreadsheet\/","title":{"rendered":"How Can I Insert a Column into a Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I insert a column into an Excel spreadsheet?

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

Hey, HE. Let\u2019s assume you have a spreadsheet that looks something like this:<\/P>\"Microsoft \n


And, yes, it is<\/I> a very nice little spreadsheet, except for one problem. As you can see, we goofed, and we forgot to insert Dataset 3; instead, we only have Datasets 1, 2, and 4. Even worse, Dataset 4 is in the wrong spot; it needs to be moved over to column D. In other words, we need to insert a new column between columns B and C. That will move Dataset 4 over to column D (where it belongs), and give us a blank column in column C where we can enter the missing information for Dataset 3.<\/P>\n

Make sense? The only question is this: how do we insert a column in a spreadsheet?<\/P>\n

Ah, but don\u2019t worry, that\u2019s what the Scripting Guys are here for. Here\u2019s a script that writes a column header to columns A, B, and C, then inserts a new, blank column between columns B and C:<\/P>

Const xlShiftToRight = -4161<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.Cells(1,1) = “Dataset 1”\nobjWorksheet.Cells(1,2) = “Dataset 2”\nobjWorksheet.Cells(1,3) = “Dataset 4”<\/p>\n
Set objRange = objExcel.Range(“C1”).EntireColumn\nobjRange.Insert(xlShiftToRight)\n<\/PRE>\n
The script begins by defining a constant named xlShiftToRight and setting the value to -4161 (yes, minus<\/I> 4161; go figure). To insert a column (or a row) in Excel you select an existing column or row and then move all those cells either to the right (in which case you use the constant xlShiftToRight) or down (in which case you use the constant xlShiftDown, which has a value of -4121). After defining the constant we create an instance of Excel, set the Visible <\/B>property to True, then add a new workbook and a new worksheet.<\/P>\n
Next we add a header to cells A1, B1, and C1; this sets the stage for us to actually insert the new column. And we do that<\/I> using just two lines of code: <\/P>
Set objRange = objExcel.Range(“C1”).EntireColumn\nobjRange.Insert(xlShiftToRight)\n<\/PRE>\n
To begin with, we create a Range<\/B> object representing all the cells in column C, the column we need to shift over. We do that by creating a range consisting of a single cell in column C (in this case, cell C1) and then using the EntireColumn<\/B> property to extend this range to all<\/I> the cells in column C. All we have to do then is call the Insert<\/B> method, passing the constant xlShiftToRight as the sole parameter. This simply tells Excel that we want to take the specified range – column C – and move those cells over to the right. And look what happens:<\/P>\"Microsoft \n

We\u2019ve successfully inserted a new, blank column, just like we wanted to do.<\/P>\n
And before you ask, here\u2019s a similar that scripts that adds a new row<\/I> across the top of a spreadsheet:<\/P>
Const xlShiftDown = -4121<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.Cells(1,1) = “Dataset 2A”\nobjWorksheet.Cells(1,2) = “Dataset 2B”\nobjWorksheet.Cells(1,3) = “Dataset 2C”<\/p>\n
Set objRange = objExcel.Range(“A1”).EntireRow\nobjRange.Insert(xlShiftDown)\n<\/PRE>\n
Cool, huh? If this whets your appetite for Microsoft Office scripting be sure and check out the Office Space<\/B><\/A> column, which features new Office scripting tips and tricks every Tuesday and Thursday<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I insert a column into an Excel spreadsheet?— HE Hey, HE. Let\u2019s assume you have a spreadsheet that looks something like this: And, yes, it is a very nice little spreadsheet, except for one problem. As you can see, we goofed, and we forgot to insert Dataset 3; instead, we […]<\/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,711,48,49,3,5],"class_list":["post-70053","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I insert a column into an Excel spreadsheet?— HE Hey, HE. Let\u2019s assume you have a spreadsheet that looks something like this: And, yes, it is a very nice little spreadsheet, except for one problem. As you can see, we goofed, and we forgot to insert Dataset 3; instead, we […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/70053","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=70053"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/70053\/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=70053"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=70053"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=70053"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}