How Can I Insert a Column into a Spreadsheet?

ScriptingGuy1

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

— HE

Hey, HE. Let’s 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 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.

Make sense? The only question is this: how do we insert a column in a spreadsheet?

Ah, but don’t worry, that’s what the Scripting Guys are here for. Here’s a script that writes a column header to columns A, B, and C, then inserts a new, blank column between columns B and C:

Const xlShiftToRight = -4161

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1) = “Dataset 1”
objWorksheet.Cells(1,2) = “Dataset 2”
objWorksheet.Cells(1,3) = “Dataset 4”
Set objRange = objExcel.Range(“C1”).EntireColumn
objRange.Insert(xlShiftToRight)

The script begins by defining a constant named xlShiftToRight and setting the value to -4161 (yes, minus 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 property to True, then add a new workbook and a new worksheet.

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 using just two lines of code:

Set objRange = objExcel.Range(“C1”).EntireColumn
objRange.Insert(xlShiftToRight)

To begin with, we create a Range 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 property to extend this range to all the cells in column C. All we have to do then is call the Insert 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:

We’ve successfully inserted a new, blank column, just like we wanted to do.

And before you ask, here’s a similar that scripts that adds a new row across the top of a spreadsheet:

Const xlShiftDown = -4121

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1) = “Dataset 2A”
objWorksheet.Cells(1,2) = “Dataset 2B”
objWorksheet.Cells(1,3) = “Dataset 2C”
Set objRange = objExcel.Range(“A1”).EntireRow
objRange.Insert(xlShiftDown)

Cool, huh? If this whets your appetite for Microsoft Office scripting be sure and check out the Office Space column, which features new Office scripting tips and tricks every Tuesday and Thursday

ScriptingGuy1

Follow