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.
Range("A1").EntireColumn.Insert
Helpful Links: Add Developer Tab | Visual Basic Editor | Run a Macro | Personal Macro Workbook
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.
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 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.
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, 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
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Select a Range/Cell using VBA in Excel