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.
- First, specify a cell using the range object.
- Now, enter a dot (.) to get the list of properties and methods.
- After that, select the “Entire Column” property or type it.
- In the end, again enter a dot (.) and select the “Insert” method or type it.
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
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.
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 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 count of columns from the cell A1 and the column where you want to add columns from the 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.
When you run the above code, it inserts a new column before the 7th column. Now, what happens, when you insert a column before the 7th column that new column becomes the 7th column, and then the second line of code clear 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
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells in VBA
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using a VBA Code
- 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)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells
⇠ Back to What is VBA in Excel
Helpful Links – Developer Tab – Visual Basic Editor – Run a Macro – Personal Macro Workbook – Excel Macro Recorder – VBA Interview Questions – VBA Codes
1 thought on “VBA Insert Column (Single and Multiple)”
Why would you use xlShiftDown when inserting Columns?