Insert Columns using VBA in Excel

In the previous article, we learned to insert new rows in a spreadsheet using VBA. Today we will learn to insert columns in spreadsheets with the help of Visual Basic. Adding new columns is similar to adding new rows with minor syntax differences between the two.

So let us start learning.

Inserting New Columns using VBA

We can use the Insert method with the EntireColumn property of the Range object to insert a new column at the left of specified column in the spreadsheet. The EntireColumn property is used to refer to the complete column of the cell.

The Insert method has the following two optional arguments.

• Shift – This argument is used to specify where we wish to shift the current column after a new column is inserted. It is irrelevant as we cannot shift the column to the left when new column is inserted
• CopyOrigin – This argument specifies from which column we wish to copy the formatting to paste it to the newly added column . It has two possible values.
  • vbFormatFromLeftOrAbove (0)- This is the default value of this argument and it copies the formatting of new column from the left adjacent column.
  • vbFormatFromRightOrBelow (1) – The value formats the newly inserted column based on the column to the right of it.

You can use the following practice workbook to follow along.

Insert Single Column

In this section of the blog, we will learn to insert single column in our spreadsheet.

Insert Column Before ActiveCell

We can insert a new column to the left of active cell. Follow these steps to implement the insertion of new column to the left of column containing active cell when the command button is clicked.

• Go to the Developer tab on the ribbon and click on the Insert button. Choose the Command Button under the Form Controls.

• Drag the mouse cursor on the excel spreadsheet and when you release the mouse after dragging, the Assign Macro dialog box opens. Click on New Button.

• This opens the code window. We can write the code which is executed when the button is clicked. Use the following code.

Sub Button1_Click()
'add a new column to the left of active cell
ActiveCell.EntireColumn.Insert
End Sub

• Close the Visual Basic Editor. Insert Data in range A1:E11. Move the active cell and click on the command button to execute the above code.

Insert Column Based on Cell Value

We can add a column based on the cell value. Follow these steps to implement the working.

• Assign cells A1 and A2 to enter the column number to the left to which we wish to add a new column.

• The value of cell A2 will be used in the code to insert a new column.
• Take a new command button and add the following code to be executed when the command button is clicked.

Sub Sheet2_Button1_Click()
'add a new column to the left of cell specified in the cell A2
Dim col_num As Integer
'assign the value of column number from cell A2 to variable
col_num = Range("A2").Value
If col_num < 16384 And col_num >= 1 Then
'columns function to refer to the column specified by col_num
Columns(col_num).Insert
End If
End Sub

• Now you can enter the column number in cell A2 and click on the command button to insert the column.

Insert Multiple Columns to the Left of the Active Cell

We can insert multiple columns to the left of the active cell by using the For Loop. We will first ask how many new columns need to be inserted with the help of InputBox and then insert the columns to the left of Active Cell with the help of For Loop. Use the following Sub Procedure.

Sub insertMultipleColumn()
'insert multiple columns using VBA in excel
Dim col As Integer
col = inpuptbox("Enter the number of columns to add")
For i = 1 To col
ActiveCell.EntireColumn.Insert
Next i
End Sub

• Assing the above Macro to a command button and then click on the command button to run this code.

This is how we insert multiple columns using VBA.

Clear Formatting After Inserting New Column

There are times when we do not want to copy and paste formatting of adjacent colums to the newly added column . In this case, we can use the ClearFormats method to remove the formatting just after adding the new column. Use this sub procedure to add the new column to the left of active cell and then remove its formatting.

Sub exInsertColumnNoFormatting()
'insert a new column with zero formatting
ActiveCell.EntireColumn.Insert
ActiveCell.EntireColumn.ClearFormats
End Sub

This brings us to end.

Thank you for reading. ❤