VBA Insert Column (Single And Multiple) - Excel Champs

Contents hide Insert a Single Column using VBA Insert Multiple Columns with VBA Insert Column After a Specific Column Insert Columns Based on the Cell Values Insert a Column without Formatting Insert Copied Column Related Tutorials

In VBA, you can use the EntireColumn.Insert to add a new column into your worksheet right next to the column you’re currently working with. When you use it, everything in your existing columns shifts over to make space for the new, empty column.

In this tutorial, we will look at how to insert a column using a VBA code in Excel. We will also explore what are the different ways to write a macro for this.

Insert a Single Column using VBA

To insert a column using a VBA code, you need to use the “Entire Column” property with the “Insert” method. With the entire column property, you can refer to the entire column using a cell and then insert a new column. By default, it will insert a column before the cell that you have mentioned.

1. First, specify a cell using the range object.
2. Now, enter a dot (.) to get the list of properties and methods.
3. After that, select the “Entire Column” property or type it.
4. In the end, again enter a dot (.) and select the “Insert” method or type it.

Range("A1").EntireColumn.Insert

Your code is ready here to insert a column. Now when you run this code, it will instantly insert a new column before the column A.

Insert Multiple Columns with VBA

There are two ways to insert multiple columns in a worksheet that I have found. The first is the same insert method that we have used in the above example. With this, you need to specify a range of columns whose count is equal to the count of the column you want to insert.

Now let’s say you want to insert 5 columns after column C in the case you can use a code like the following.

Range("C:G").EntireColumn.Insert

To be honest, I haven’t found this method quite useful because you need to change the range if you want to change the code itself. So, here’s the second method.

'variables to use in the code Dim iCol As Long Dim iCount As Long Dim i As Long 'to get the number of columns that you want to insert with an input box iCount = InputBox(Prompt:="How many column you want to add?") 'to get the column number where you want to insert the new column iCol = InputBox _ (Prompt:= _ "After which column you want to add new column? (Enter the column number)") 'loop to insert new column(s) For i = 1 To iCount Columns(iCol).EntireColumn.Insert Next i

When you run this code, it asks you to enter the number of columns that you want to add and then the column number where you want to add all those new columns. It uses a FOR LOOP (For Next) to enter the number of columns that you have mentioned.

Insert Column After a Specific Column

To insert a new column after a specific column, you can use the Insert method and specify the column after which you want to add the new column.

Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

The above code tells Excel to insert a new column to the right of column E. The Shift:=xlToRight moves all existing columns to the right to make space for the new one. The CopyOrigin:=xlFormatFromLeftOrAbove copies the formatting from the column to the left of the new column.

Insert Columns Based on the Cell Values

If you want to insert columns based on a cell value, then you can use the following code.

Dim iCol As Long Dim iCount As Long Dim i As Long iCount = Range("A1").Value iCol = Range("B1").Value For i = 1 To iCount Columns(iCol).EntireColumn.Insert Next i

When you run this macro, it takes the count of columns from cell A1 and the column where you want to add columns from cell B1.

Insert a Column without Formatting

When you insert a column where the above column has some specific formatting, in that case, the column will also have that formatting automatically. And the simplest way to deal with this thing is to use clear formats. Consider the following code.

Columns(7).EntireColumn.Insert Columns(7).ClearFormats

When you run the above code, it inserts a new column before the 7th column. Now, what happens, is when you insert a column before the 7th column that new column becomes the 7th column, and then the second line of code clears the formats from it.

Insert Copied Column

You can also use the same method to copy a column and then insert it somewhere else. See the following code.

Application.CutCopyMode = False With Worksheets("Data") .Columns(5).Copy .Columns(9).Insert Shift:=xlShiftDown End With Application.CutCopyMode = True What is VBA

Related Tutorials

• Count Rows using VBA in Excel
• Excel VBA Hide and Unhide a Column or a Row
• Find Last Row, Column, and Cell using VBA in Excel
• Insert a Row using VBA in Excel
• ActiveCell in VBA in Excel
• Special Cells Method in VBA in Excel
• UsedRange Property in VBA in Excel

• VBA AutoFit (Rows, Column, or the Entire Worksheet)
• VBA ClearContents (from a Cell, Range, or Entire Worksheet)
• VBA Copy Range to Another Sheet + Workbook
• VBA Enter Value in a Cell (Set, Get and Change)