How to Delete Column and Shift Left Using VBA in Excel (5 Methods)

While working with a large Microsoft Excel, sometimes unnecessary data will appear in front of us, and we need to delete those unnecessary data in Excel. We also can do that by applying VBA Macros. Today, in this article, we’ll learn five quick and suitable ways to VBA delete column shift left in Excel effectively with appropriate illustrations.


VBA to Delete Column and Shift Left (Quick View)

Sub Delete_Column_ShiftLeft()
    Range("C5:C14").Delete Shift:=xlToLeft
End Sub

Use a VBA Code to Delete Column and Shift Left in Excel


Download Practice Workbook

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


5 Suitable Ways to Delete Column and Shift Left Using VBA in Excel

Let’s say, we have a dataset that contains information about several students of Cantonment Public School. The names of the students, their identification number, securing marks in Physics, Chemistry, and Mathematics are given in columns B, C, D, E, and F respectively. We will delete some arbitrary columns shift left with VBA Macros. Here’s an overview of the dataset for today’s task.

excel vba delete column shift left


1. Use a VBA Code to Delete Column and Shift Left in Excel

In this method, we will delete the column from our dataset that contains the marks of Physics of the students by applying the VBA Code. Applying the VBA code to delete the column is time-saving also. Let’s follow the instructions below to learn!

Step 1:

  • First of all, from your Developer tab, go to,

Developer → Visual Basic

Use a VBA Code to Delete Column and Shift Left in Excel

  • After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Delete Column Shift Left will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

  • Hence, the Delete Column Shift Left module pops up. In the Delete Column Shift Left module, write down the below VBA
Sub Delete_Column_ShiftLeft()
   Range("D4:D14").Delete Shift:=xlToLeft
End Sub

Use a VBA Code to Delete Column and Shift Left in Excel

  • After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 3:

  • Further, go back to your active worksheet, and you will be able to delete column D shift left that contains the marks of Physics in your active worksheet that has been given below screenshot.

Use a VBA Code to Delete Column and Shift Left in Excel

Read More: VBA to Delete Column in Excel (9 Criteria)


2. Run a VBA Code to Delete Cells and Shift Left in Excel

After learning the Delete Column Shift Left by applying the VBA code, now, we’ll learn how to delete cells from our dataset. Please follow the steps below to learn!

Steps:

  • According to method 1, insert a new module and type the below VBA code to delete cells. The VBA code is,
Sub Delete_Cells_Shift_Left()
     Range("E7:E14").Delete Shift:=xlToLeft
End Sub

Run a VBA Code to Delete Cells and Shift Left in Excel

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

  • After that, go back to your active worksheet, and you will be able to delete cells E7 to E14 in your active worksheet that has been given below screenshot.

Run a VBA Code to Delete Cells and Shift Left in Excel

Read More: Macro to Delete Columns in Excel (10 Methods)


3. Delete Column and Shift Left with Worksheet Name Using VBA in Excel

Undoubtedly, applying the VBA code to the Delete Column shift left with the worksheet name is an easy task. From our dataset, we will perform the VBA code to delete the column with the worksheet name. Let’s follow the steps below to learn!

Steps:

  • First, place your cursor on the active sheet name, and press right-click on your mouse. A window pops up. From that window, select the View Code option.

Delete Column and Shift Left with Worksheet Name Using VBA in Excel

  • After clicking on the View Code option, a Delete Column Shift Left module pops up. Write down the below code in that module.
Sub Delete_Column_With_Worksheet_Name_Shift_Left()
        Worksheets("Grade sheet").Select
       Range("D5:E10").Delete Shift:=xlToLeft
End Sub

  • After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

  • Hence, go back to your active worksheet, and you will be able to delete cells with the worksheet name and shift those cells left that have been given below screenshot.

Delete Column and Shift Left with Worksheet Name Using VBA in Excel

Read More: How to Delete Columns without Losing Formula in Excel (3 Easy Steps)


Similar Readings:


4. Apply a VBA Code to Delete Blank Column and Shift Left in Excel

In this method, we will delete blank columns from our dataset by applying a simple VBA code. Let’s follow the steps below to learn!

Step 1:

  • First of all, look at our dataset. The dataset contains blank columns C, E, and G. We’ll delete these blank columns.

Apply a VBA Code to Delete Blank Column and Shift Left in Excel

  • According to method 1, insert a new module and type the below VBA code to delete blank columns. The VBA code is,
Sub Delete_Blank_Columns_Shift_Left()
            Dim R As Integer
            For R = 1 To 3
            Columns(R + 2).Delete
            Next R
End Sub

Apply a VBA Code to Delete Blank Column and Shift Left in Excel

  • After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

  • Hence, go back to your active worksheet, and you will be able to delete blank columns and shift left that have been given below screenshot.

Apply a VBA Code to Delete Blank Column and Shift Left in Excel

Read More: Can’t Delete Blank Columns in Excel (3 Problems & Solutions)


5. Delete Entire Column with Blank Cells and Shift Left in Excel

After learning how to delete blank columns, now we will learn how to delete blank cells by using VBA code. Applying the VBA code to delete blank cells shift left is the easiest and time-saving way also. Let’s follow the steps below to learn!

Step 1:

  • First, have a look at our dataset. The dataset contains blank cells C8, D11, and E8. We’ll delete these blank cells along with the columns that contain those blank cells.

Delete Entire Column with Blank Cells and Shift Left in Excel

  • According to method 1, insert a new module and type the below VBA code to delete blank columns. The VBA code is,
Sub Delete_Blank_Cells_Shift_Left()
            Range("B4:F14").Select
            Selection.SpecialCells(xlCellTypeBlanks).Select
            Selection.EntireColumn.Delete
End Sub

Delete Entire Column with Blank Cells and Shift Left in Excel

  • After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

  • Hence, go back to your active worksheet, and you will be able to delete blank cells and shift left that have been given below screenshot.

Delete Entire Column with Blank Cells and Shift Left in Excel

Read More: How to Delete Multiple Columns in Excel


Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon


Conclusion

I hope all of the suitable methods mentioned above to delete columns with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo