Excel VBA: Cut and Insert Column (5 Examples)

This article illustrates how to cut a column and then insert it again based on different criteria using VBA in Excel. Normally, we can do the same task using a keyboard shortcut or right-clicking the mouse. Similarly, using VBA code facilitates us with cutting and inserting single or multiple columns to the same sheet or to another sheet and more.

Cut and Insert a Column Using VBA in Excel: 5 Suitable Examples

Excel facilitates us with two methods to use in VBA code to cut and insert a Range object (a cell, a row, a column, or a range of selected cells). We’ll use the Range.Cut method to cut single or multiple columns and then the Range.Insert method to insert into the worksheet.

To illustrate the examples, we’re going to use the following dummy dataset with 5 differently colored columns.

Write Code in Visual Basic Editor

To cut and insert columns, we need to open and write VBA code in the visual basic editor. Follow the steps to open the visual basic editor and write some code there.

• Go to the Developer tab from the Excel Ribbon.
• Click the Visual Basic option.

• In the Visual Basic for Applications window, click the Insert dropdown to select the New Module option.

Now put your code inside the visual code editor and press F5 to run it.

1. Cut and Insert a Single Column Using VBA in Excel

Task: We want to cut Column C (Column 2 in the dataset) and show it in column I.

Code: Let’s copy and paste the VBA code into the visual basic editor and press F5 to run it.

Sub CutInsertColumn()
    Columns("C:C").Cut
    'Alternatively use
    'Columns(3).Cut
    Columns("J:J").Insert
End Sub

Code Explanation: Although we want to show Column C (Column 2) in Column I, we need to input Column J in the Range.Insert method argument. If we cut Column C and then insert it into another place, it’ll leave a blank space. To fill this blank space the columns right to the black space will shift one column left automatically.

Output: The above code has successfully shifted column C to column I.

Another Case: To get a clear understanding of the above code, we’re going to illustrate another example. Here we cut Column C (Column 2 in the dataset) to Column F.

Sub CutInsertColumn()
Range("C:C").Cut
Range("G:G").Insert
End Sub

Read More: Excel VBA: Move Column Based on Header

2. VBA Code to Cut and Insert Multiple Columns in Excel

Task: We want to cut columns C, and D (columns 2, and 3 in the screenshot) and show them in columns H and I.

Code: Let’s copy and paste the VBA code into the visual basic editor and press F5 to run it.

Sub CutInsertColumn()
    Columns("C:D").Cut
    Columns("J:J").Insert
End Sub

Output: The above code has successfully cut and inserted columns C and D into columns H and I. Here, Column 4 and Column 5 have shifted to the left and filled the blank spaces left by Column 2 and Column 3.

Read More: How to Move Multiple Columns in Excel

3. Cut and Insert to Replace Single Column Using VBA in Excel

Task: We want to cut Column C (Column 2 in the dataset) and insert it in Column I. This time the blank space left by Column C will not be filled by other columns.  

Code: Let’s copy and paste the VBA code into the visual basic editor and press F5 to run it.

Sub CutInsertColumn()
Range("C:C").Cut Range("I:I")
End Sub

Output: The above code has successfully shifted Column C to Column I.

Another Case: Run the following code to replace Column 4 with Column 2 in the dataset.

Sub CutInsertColumn()
Range("C:C").Cut Range("E:E")
End Sub

4. Run a VBA Code to Cut and Insert to Replace Multiple Columns

Task: We want to cut columns C and D (columns 2 and 3 in the dataset) and insert them in columns I and J. This time the blank space left by columns C and D will not be filled by other columns.  

Code: Let’s copy and paste the VBA code into the visual basic editor and press F5 to run it.

Sub CutInsertColumn()
Range("C:D").Cut Range("I:I")
End Sub

Output: The above code has successfully cut and inserted columns C and D into columns H and I.

5. Cut and Insert a Column Into Another Sheet with Excel VBA

Task: We want to cut Column B (Column 1 in the dataset) from the worksheet named Sheet1 and insert it in Column B in the worksheet named Sheet2. This time the blank space left by Column C will remain as it is.

Code: Let’s copy and paste the VBA code into the visual basic editor and press F5 to run it.

Sub CutInsertColumn()
Sheets("Sheet1").Range("B:B").Cut Sheets("Sheet2").Range("B:B")
End Sub

Output: The above code has successfully cut and inserted Column B from Sheet1 into Column B of Sheet2.

Notes

To view the code associated with each example, click the right button on the sheet name and select the View Code option.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Now, we know how to cut and insert columns using VBA in Excel with the help of suitable examples.  Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Article

• How to Move Columns in Excel without Overwriting
• How to Move Columns in Excel Table