Excel VBA to Delete and Shift Left Any Cell or Column

Excel is a powerful tool for data analysis and management. One of Excel’s most valuable features is the ability to automate activities using Visual Basic for Applications (VBA). With the help of VBA, you can perform tough and time-consuming tasks with just a few clicks. While dealing with a dataset, you may have some data that you don’t want. To erase this unnecessary data, you can use the Delete command in the VBA code. But when you delete any data, the cell will become empty. In that case, you need to shift the other cells or columns left to their actual places. In this article, we are going to explain how to delete and shift left in Excel VBA. We will walk through the methods involved in this task to make it automatic. Also, this task improves your workflow and increases productivity. So let’s get started.

Here, we attached a summarized video of our task for better visualization.

What Do ‘Delete and Shift Left’ Commands Mean in Excel VBA?

The Delete command in Excel VBA means to delete an entire column, row, or cell. The Shift left means to shift the other cells where the delete operation takes place. This implies that any cells immediately adjacent to the ones that were removed will be shifted over to fill the vacant space.

For example, you take a dataset from cell B4 to E12. Now you want to delete columns C and D then column E will be moved to the left side. So, it can be said that the adjacent cells or columns of the removed items will take place when you use the shift left statement.

The simple VBA code you should follow to delete the cell value and shift left is stated below.

Sub DeleteCells()
Dim myRange As Range
Set myRange = Range("C4:D12") 'change to your desired range
myRange.Delete Shift:=xlToLeft
End Sub

The following code will delete the range you wish to delete based on your desired range. Then, the columns existing on the right side of the deleted columns will replace the left.

How to Launch VBA Editor in Excel

You can launch the VBA editor by following these simple methods. Here, we use the developer tab to insert the module where we write the code. Follow the simple steps to open the VBA editor.

• Initially, hover over to the Developer tab >> choose Visual Basic.

• Then, the Visual Basic Editor launches and goes to the Insert tab >> Module >> Module1.

Excel VBA to Delete and Shift Left: 3 Examples with Cases

While you’re working at a business company, you may have unnecessary data in your database that you want to delete and shift to other values. In Excel VBA, you can do it for columns, rows, or cells.

To show examples of how to do so, we have taken a dataset of Information from ABC Car Company. Here, we delete some data and replace the data with the value of its right side with the help of shifting.

Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.

1. Delete Cells and Shift Left with VBA in Excel

Sometimes, you may have a cell or a range of cells that are shifted to the right side, but you need to move them to the left side of your dataset and delete the existing value in the cell. You can do it for a specific value in your dataset as well. We have demonstrated to you both the systems to delete and shift left the cells.

1.1 Delete Cell for Any Value

Look at the dataset very carefully, here the value of D5 is shifted to the right side of the Price column. But you need to delete the D5 cell value, which is blank, and move the value of cell E5 by shifting it to the left. Here in the code, the Delete command will delete the desired cell and then the cell will be shifted to the left.

To do this, we have conducted the following VBA code.

Sub Delete_Cell()
Range("F5").Delete Shift:=xlShiftToLeft
End Sub

Here, the Delete command removes the value of cell D5 and Shift:=xlShiftToLeft shifts the value to the left.

1.2 Deletes Cells with a Specific Value

You can remove a specific value from others by using VBA. In our dataset, we want to replace all the Manufacturer names “Honda” with the name “Nissan”. We have used a VBA code where the search range is selected with “Honda” and then it will be replaced by “Nissan”.

To do this, we have inserted a new module and written up the VBA code there.

Sub Delete_Specific_Value()
Dim searchRange As Range
On Error Resume Next
Set searchRange = Application.InputBox("Please select a range", _
"Select Range", Type:=8) 'Select your preferred range
On Error GoTo 0
If Not searchRange Is Nothing Then
Dim deleteCell As Range
Set deleteCell = searchRange.Find("Honda", _
LookIn:=xlValues) 'Search for a specific value
Do While Not deleteCell Is Nothing
deleteCell.Delete Shift:=xlShiftToLeft
Set deleteCell = searchRange.Find("Honda", _
LookIn:=xlValues)
Loop
End If
End Sub

In a chosen range, this VBA function eliminates every occurrence of the value “Honda“.

For better visualization, we have attached the video of the whole process here.

2. Delete Selected Range/Selection and Shift Left with Excel VBA

You can also delete a range of cells by selecting the range. Then the selected range value will be deleted, and the value adjacent to the deleted cells will be placed there.

For example, follow the dataset here. In the dataset, the D9:D14 cells are blanked, and their corresponding values exist on the right side. We want to delete the value for this range of cells.

Use the following VBA code to do so.

Sub DeleteAndShiftLeft_range()
Dim searchRange As Range
On Error Resume Next 'If error occurs then go to next line
Set searchRange = Application.InputBox("Please select a range", _
"Select Range", Type:=8) 'Select a suitable range
On Error GoTo 0
If Not searchRange Is Nothing Then
searchRange.Delete Shift:=xlShiftToLeft
End If
End Sub

This VBA script removes the chosen range and moves the surrounding cells to the left to fill the void.

Please follow the video for better clarification.

Read More: Excel VBA to Clear Contents of Range

3. Delete Columns and Shift Data to Left

It is possible to delete columns in a worksheet with the delete command. Also, you have selected the Range property. This can be useful to remove unwanted data or restructure a worksheet to better fit your needs. It can be done in single columns, multiple columns, a specific cell value, or blank columns. We have covered all these methods using VBA.

3.1 Delete Single Column and Shift Data to Left

In your dataset, you may need to delete one column that is not suitable for you. In our dataset, we want to delete the “Year” column. We used a VBA code where the Delete command is used to delete the entire column D.

The following VBA code will do the job in this regard.

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

Here, we first stated a sub-procedure named “Delete_Column_ShiftLeft” We select column D with the range property and delete the column with the Delete command. Also, it shifts the other columns to the left

3.2 Delete Multiple Columns and Shift Data to Left

You can do the same operation with multiple columns. In our dataset, we want to delete the Model and Year columns and shift the Price column to the left.

Follow the below VBA code to delete multiple columns and shift the other columns to the left.

Sub Delete_MultipleColumns_ShiftLeft()
Range("D:E").Delete Shift:=xlToLeft
End Sub

Here, we have assigned columns D and E with the range property and deleted them with the Delete command.

The below video will explain the method more accurately.

3.3 Delete Columns Based on Cell Value and Shift Left

When we need to delete a column that has acquired a specific cell value, we can also do that with the help of the VBA code. You need to fix the value you want to delete, and the VBA code will find it where it exists.

In our dataset, we want to delete the column where the model name LX exists. When you run the code, a prompt appears. Select the desired range from there. After pressing OK, you will get another prompt where you need to insert the value that you want to find. for which the column will be deleted. Finally, press OK, and you will get the selected column deleted, like the image below.

Follow up on the VBA code to do so.

Sub Delete_Column_Specific_Value()
Dim searchRange As Range
On Error Resume Next
Set searchRange = Application.InputBox("Please select a range", _
"Select Range", Type:=8)
On Error GoTo 0
Dim searchValue As String
'Input the Search value that you looking for
searchValue = InputBox("Enter the search value:")
If Not searchRange Is Nothing And searchValue <> "" Then
Dim deleteColumn As Range
Set deleteColumn = searchRange.Find(searchValue, _
LookIn:=xlValues, LookAt:=xlWhole)
Do While Not deleteColumn Is Nothing
deleteColumn.EntireColumn.Delete Shift:=xlShiftToLeft
Set deleteColumn = searchRange.Find(searchValue, _
LookIn:=xlValues, LookAt:=xlWhole)
Loop
End If
End Sub

This VBA code deletes all columns that contain a specific value in a selected range. Here’s how it works:

To make the process more clear, we have attached a video.

3.4 Delete Column Where Cell Is Blank and Shift Data to Left

If you need to delete a column that contains blanks, you can use the same code, but you need to change the search value and set it to blanks. As you can see, we have some blank spaces in the “Manufacturer” column. So, we need to delete the column and shift the other values to the left.

Sub DeleteAndShiftLeft_BlankColumns()
Dim searchRange As Range
On Error Resume Next
Set searchRange = Application.InputBox("Please select a range", _
"Select Range", Type:=8)
On Error GoTo 0
Dim blankColumn As Range
Set blankColumn = searchRange.Find("", LookIn:=xlValues, _
LookAt:=xlWhole) 'Looking for the blank cells
If Not blankColumn Is Nothing Then
blankColumn.EntireColumn.Delete Shift:=xlShiftToLeft
End If
End Sub

The code is almost the same as the code we stated in Method 3.3. The main difference is we set the searchRange. Find the blank statement. So, it finds the blank in the selected dataset and deletes the corresponding column where the blank occurs.

Subsequently, when you press the F5 key, you will get a prompt to select the range.

After selecting the range and pressing OK and you get your desired result.

How to Delete Row with Excel VBA

In the above discussion, we have tried to cover the topics of deleting and shifting left. But you can delete the row and shift the cell up with the help of VBA. You can delete one row or multiple rows with a specific value.

1. Delete One Row with the Row Number

We have used the same dataset we used in the above method. We will input a row number, and the delete command will delete the rows. Following the VBA code does our job. You can see the row having “Sentra” has been deleted with the help of the VBA code.

Sub deleteRow()
Dim rowToDelete As Long
rowToDelete = InputBox("Enter the row number to delete:")
If rowToDelete > 0 And rowToDelete <= Rows.Count Then
Rows(rowToDelete).Delete Shift:=xlUp
End If
End Sub

Here, the code allows the user to select a row number with the help of the InputBox. Then the row will be deleted. And the other cells will shift up.

When you run the code with the F5 key, you get a dialog box where you need to enter the row number that you want to delete. Here, we put row 9 to delete.

2. Delete Rows Based on a Specific Value and Shift Row Up

You can also delete a set of rows where a specific value exists. In our dataset, we want to delete the rows where the value “Toyota” exists. The selected rows will be deleted, and the other rows will be moved up. This simple VBA code will search for the desired value and then delete the row where this value exists.

Sub DeleteRowsAndShiftUp()
Dim searchRange As Range
On Error Resume Next
Set searchRange = Application.InputBox("Please select a range", _
"Select Range", Type:=8)
On Error GoTo 0
Dim deleteRow As Range
Set deleteRow = searchRange.Find("Toyota")
Do While Not deleteRow Is Nothing
deleteRow.EntireRow.Delete Shift:=xlUp
Set deleteRow = searchRange.Find("Toyota")
Loop
End Sub

After that, you have to run the code with the F5 key, and the dialog box appears to select the range. After selecting the range, you will have the desired range removed.

Read More: Excel VBA: Clear Contents If Cell Contains Specific Values

Frequently Asked Questions (FAQs)

• What happens if I use “delete shift left” on a cell that is merged with other cells?

If you use the “delete shift left” command on a merged cell, the contents will be deleted but the neighboring cells won’t be shifted to the left. The united cell will instead be divided into many cells.

• What is the difference between “Delete Shift Left” and “Delete Shift Up” in Excel VBA?

The commands “delete shift left” and “delete shift up” in Excel VBA both delete cells or ranges and move the remaining cells or rows to fill up the empty area. In contrast to “delete shift up,” which pushes the remaining rows up to fill the void, “delete shift left” shifts the remaining cells to the left to accomplish the same thing.

• Is there a way to disable the “delete shift left” confirmation dialog in Excel VBA?

Yes, by changing the “Application.DisplayAlerts” value to False, you may make Excel VBA suppress the “delete shift left” confirmation box.

• What is the difference between Clear and Delete commands in Excel VBA?

The Clear command removes cell contents and sometimes formatting if that is specified in the code. But the Delete command deletes the entire row/column/cell when we use it. It includes the formatting and shifts the cell to fill the gap.

The Delete command shows some errors, so it will delete all the formatting. If you make any errors, then you need to change all the formatting, while the Clear command only removes the particular cell contents. That’s why it is helpful in error handling.

Things to Remember

• The range of cells you want to eliminate must be specified, and this is crucial. Excel will prompt the user to choose the range if you don’t, which might not be what you want.
• The Delete and Clear methods are the two ways to delete cells in Excel VBA. Whereas the Clear technique merely clears the contents of the cells, the Delete method deletes the cells and moves the remaining cells to the left.
• Use the Application if you want to ask the user for permission to delete anything before it actually happens. To display a message box, use the DisplayAlerts property.
• Errors can occur if you attempt to delete cells that are protected or have data validation rules. In order to prevent your code from breaking, be sure to handle these errors appropriately.

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

Wrapping Up

So, this is all about the use of the Excel VBA to Delete and Shift Left commands. With Excel VBA, the powerful and crucial procedures Delete and Shift Left allow users to change data and improve their workflows. These procedures are frequently used to eliminate unnecessary data, transfer the remaining data to fill in the void, and make sure that the resultant data is still ordered and understandable. Although there are other methods to complete similar operations in Excel VBA, utilizing Delete and Shift Left is frequently the fastest and most reliable approach. Whichever level of Excel VBA user you are, learning these actions may help you save a ton of time and effort when processing data. If you find any kind of difficulty using the delete command, you can contact us by commenting on our site. Hopefully, you understand the process clearly. Thank you for your patience in reading the article.

Related Articles

• How to Clear Contents of a Sheet with Excel VBA
• Excel VBA to Clear Contents of Named Range
• How to Clear Cells in Excel with Button
• How to Clear Contents Without Deleting Formulas Using VBA in Excel