Excel VBA: Cut and Insert Column (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel VBA Cut and Insert Column

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

Excel VBA Cut and Insert Column

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.

Excel VBA Cut and Insert Column

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

Excel VBA Cut and Insert Column


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.

Excel VBA Cut and Insert Column

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.

Excel VBA Cut and Insert Column


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.

Excel VBA Cut and Insert Column

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

Excel VBA Cut and Insert Column


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.

Excel VBA Cut and Insert Column

Read More: Insert Column with Name in Excel VBA


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 will help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo