Md. Abdur Rahim Rasel
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 5 quick and suitable ways to VBA delete a column and 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
How to Delete Column and Shift Left Using VBA in Excel: 5 Methods
Let’s say, we have a dataset that contains information about several students of a school. The names of the students, their identification numbers, and securing marks in Physics, Chemistry, and Mathematics are given in columns B, C, D, E, and F respectively. We will delete some arbitrary columns and shift left with VBA. Here’s an overview of the dataset for today’s task.
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!
- Go to Developer > Visual Basic.
- 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, go to Insert > Module.
- Hence, the Delete Column Shift Left module pops up. In the Delete Column Shift Left module, write down the below VBA code.
Sub Delete_Column_ShiftLeft()
  Range("D4:D14").Delete Shift:=xlToLeft
End Sub
- Go to Run > Run Sub/UserForm.
- 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.
Read More: VBA to Delete Column in Excel
2. Run a VBA Code to Delete Cells and Shift Left in Excel
After learning the delete column and shift left by applying the VBA code, we’ll learn how to delete cells from our dataset.
- According to method 1, insert a new module and type the below VBA code to delete cells.
Sub Delete_Cells_Shift_Left()
    Range("E7:E14").Delete Shift:=xlToLeft
End Sub
- Go to Run > Run Sub/UserForm.
- 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.
Read More: How to Delete Columns with Specific Text in Excel
3. Delete Column and Shift Left with Worksheet Name Using VBA in Excel
Undoubtedly, applying the VBA code to the delete column and 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!
- Place your cursor on the active sheet name, and press right-click on your mouse.
- Select the View Code option from the context menu.
- 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 > Run Sub/UserForm.
- 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.
Read More: How to Delete Multiple Columns by Number Using VBA in Excel
Similar Readings
- Delete Column in Excel Without Affecting Formula
- How to Delete Multiple Columns in Excel with Condition
- How to Delete Empty Columns with Header in Excel
- VBA Macro to Delete Columns Based on Criteria in Excel
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.
- Look at our dataset. The dataset contains blank columns C, E, and G. We’ll delete these blank columns.
- According to method 1, insert a new module and type the below VBA code to delete blank columns.
Sub Delete_Blank_Columns_Shift_Left()
           Dim R As Integer
           For R = 1 To 3
           Columns(R + 2).Delete
           Next R
End Sub
- 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.
Read More: [Solved!] Can’t Delete Extra Columns in Excel
5. Delete Entire Column with Blank Cells and Shift Left in Excel
After learning how to delete blank columns, we will learn how to delete blank cells by using VBA code. Applying the VBA code to delete blank cells and shift left is the easiest and time-saving way.
- 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.
- According to method 1, insert a new module and type the below VBA code to delete blank columns.
Sub Delete_Blank_Cells_Shift_Left()
           Range("B4:F14").Select
           Selection.SpecialCells(xlCellTypeBlanks).Select
           Selection.EntireColumn.Delete
End Sub
- Go to Run → Run Sub/UserForm.
- Go back to your active worksheet, and you will be able to delete blank cells and shift left that have been given below screenshot.
Read More: How to Delete Columns Based on Header Using VBA in Excel
Things to Remember
👉 You can pop up the Microsoft Visual Basic for Applications window by pressing Alt + F11.
👉 If the Developer tab is not visible in your ribbon, you can make it visible. Go to, File > Option > Customize Ribbon.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
