Delete Row with VBA and Shift Up Cells in Excel

Looking for ways to delete row with VBA and shift up cells in Excel? Then, this is the right place for you.

When you are working with a large Excel worksheet, you may need to clean some unnecessary data. In that case, you will need to delete some rows from the worksheet and shift the remaining rows up to fill the gap left by the removed rows. This process helps to automate tasks and clean up data in a wide range because manually deleting can be tedious and time-consuming work, especially when you need to remove multiple rows at a single time.

However, Excel VBA provides you with enormous ways to serve your purpose in different cases.  You can delete single or multiple rows based on certain criteria and conditions and make the task automotive with just a button click with the help of VBA. Additionally, the article will cover other related topics, such as how to delete multiple rows automatically or how to delete rows based on specific criteria, giving you the tools you need to efficiently and effectively manage your Excel spreadsheets.

Let’s see a quick overview of how we can delete row and shift up with Excel VBA.

Download Practice Workbook

How to Launch VBA Editor in Excel

For executing VBA, you need to activate the Developer tab on the Ribbon if you haven’t done it before. After launching the Developer tab on the Home screen, launch the Visual Basic Editor window.

• Go to the Developer tab and select Visual Basic under the Code section.

Alternative Command: Pressing ALT+F11 will also take you to the VBA window.

There are 3 ways for inserting code in the Visual Basic Editor window.

• Now, the Visual Basic Editor window will show up on the screen. Click the Insert tab of the VBA window and select Module.

• You will see a Module window has popped up right beside the Project – VBAProject section. You have to insert the code you want to execute in this window.

You can either use multiple Modules for different Macros or insert your Macros one after another in the same Module. If you have different macros for serving different purposes, then it is preferable to use different Modules as it will help find your macros quickly.

Delete Row with VBA and Shift Up Remaining Cells (11 Examples)

In many cases, we will need to delete single or multiple rows from the Excel spreadsheet considering several terms and conditions. We may need to delete a specific row or find specific data in the spreadsheet and delete the corresponding row. In the next section, we will try to show 11 different real-life situations for deleting rows.

1. Removing Specific Row and Shift Up Remaining Cells with VBA

When you need to delete a specific row (either single or multiple) from the worksheet, you can just mention the row number or range of rows within your VBA code

1.1. Removing Single Row

In the above picture, we have a dataset of sales information displaying different information about some sales representatives like their age, gender, sale amount, date of sale and address.

In Row no. 8 of the Excel sheet, we can see it’s displaying information about Tom. If we want to delete the 8th row, we have to specify it within the VBA code.

In that case, the code will be as followed.Code:

Sub RemovingSingleRow()
Rows(8).EntireRow.Delete
End Sub

To execute the code,  click Run on the toolbar options of the VBA window.

Alternate: You can also run the code by pressing ALT+F8 which will open a macro window. From the window, select your Macro and click Run.

1.2. Removing Multiple Rows

In case of removing multiple rows, the procedure is the same as for a single row. Specify the row number in the code. and this will delete the specified rows.

Code:

Sub RemovingMultipleRows()
Rows(9).EntireRow.Delete
Rows(8).EntireRow.Delete
Rows(7).EntireRow.Delete
End Sub

Run the code to delete the mentioned rows from the worksheet.

However, you can also specify the range of multiple rows like the image below.

Code:

Sub DeletingMultipleRows()
Rows(7 & ":" & 9).Delete
End Sub

2. Deleting Rows and Shift Up Cells in a Range of Worksheet

In most cases, you will need to delete rows from a range of datasets where mentioning the default row number is not beneficiary.

2.1. Deleting Rows from Selection Range

Let’s say, you just need to delete some random rows from your worksheet range. In that case, the best practice is to select the range of data that you want to remove and execute the macro. The VBA code described below detects the entire row of the selection range and removes the rows.

The VBA code here we used is shown below.

Code:

Sub DeletingRowInSelection()
Selection.EntireRow.Delete
End Sub

2.2. Removing Rows from Specified Range

Another way to delete rows of the range is to mention the range in the code rather than selecting the range. For example, if we want to delete the entire rows containing the cell range C7:C9, our VBA code will detect the range of data and remove the entire rows containing these cells.

Code:

Sub DeleteingRowsFromRange()
Range("C7:C9").EntireRow.Delete
End Sub

3. Deleting Alternate Rows or Any Specific Sequential Rows and Shifting Up Remaining Cells

VBA code can be useful for quickly deleting every other row within a selected range in an Excel worksheet. Let’s say, we want to delete every odd row from a selection range for our set of data. The code we have used here describes we need to select the dataset first and then execute the code. This will delete every odd row from the selection range of data.

The VBA code used here for deleting every other row is as below.

Code:

Sub Deleting_Sequential_Rows()
Dim Rng As Range
Dim i As Long
Set Rng = Selection
For i = Rng.Rows.Count To 1 Step -2
Rng.Rows(i).EntireRow.Delete
Next i
End Sub

Dim Rng As Range
Dim i As Long

Set Rng = Selection

For i = Rng.Rows.Count To 1 Step -2
    Rng.Rows(i).EntireRow.Delete
Next i

4. Removing Rows and Shifting Up Cells with Blank Cells

In the above image, we can see that there are some blank cells in the Excel spreadsheet. If you find the rows containing blank cells unnecessary, just use VBA macro to find out the blank cells and remove the rows containing these cells. In the VBA code, we used SpecialCells property which we declared as blank.

Code:

Sub RemovingBlankRows()
Set Rng = Range("B5:H17")
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

5. Delete Row with Specific Text or Value And Shift Up

5.1. Applying For Loop Nested with If Statement

When you want to delete some rows from your worksheet containing a specific text, VBA offers you a solution in that case. Look at our dataset where we have info about male and female sales reps. We want to delete info about all “Female”. So we need to delete rows containing the word “Female”.

Use the following code to delete rows with specific text.

Code:

Sub DeletingRowsWithSpecificText()
Set Rng = Range("B5:H17")
For i = 1 To Rng.Rows.Count
If Rng.Cells(i, 4).Value = "Female" Then
Rng.Cells(i, 4).EntireRow.Delete
End If
Next i
End Sub

If Rng.Cells(i, 4).Value = "Female" Then
    Rng.Cells(i, 4).EntireRow.Delete
End If

5.2.  Filtering with Condition

This process is a little bit similar to “Delete rows with the specific text”. Notice our dataset. Here, we will filter our dataset on the basis of “Female” like the previous one. After filtering the dataset, all the info about “Female” employees will be visible only and the visible rows will be deleted consequently. All of these steps will be done with VBA macro. Just one thing to notice, keep the first cell of the data range active as we mentioned in the code

Code:

Sub DeletingFilteredRows()
ActiveCell.AutoFilter Field:=4, Criteria1:="Female"
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete
End Sub

ActiveCell.AutoFilter Field:=4, Criteria1:="Female"

ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete

6. Remove nth Row and Shift Up Cells Automatically

This method will delete the n-th row specified in the code from a selection range automatically. InputBox has been applied in the code for selecting a range. We have deleted the 2nd row from the selection range by executing the code below.

Code:

Sub Delete_Second_Row()
Dim Rng As Range
Set Rng = Application.InputBox("Select your range of data (excluding headers):", "Selection of Range", Type:=8)
If Rng.Rows.Count > 1 Then
Rng.Rows(2).Delete
End If
End Sub

Set Rng = Application.InputBox("Select your range of data (excluding headers):", "Selection of Range", Type:=8)

The Type parameter can be set to one of the following values:

• The code will delete the nth row if the selection range has more than (n-1) row. So, if you want to delete the 2nd row, your selection range must include more than 1 row.

The code above will get you the output below.

7. Delete Rows Based on Date and Shifting Up Remaining Cells

For our dataset, we can see that the date “12-Mar-2023” has been repeated 4 times. We don’t want the sales info in our dataset on that day. So, if we want to delete rows based on date, we need to figure out the rows containing this date and delete the rows.
In our code, we have used the date value as “12-Mar-2023”.

Code:

Sub DeletingRowsBasedOnDate()
Dim wks As Worksheet
Dim EndMostRow As Long
Dim i As Long
Dim dateToCheck As Date
wks = ActiveSheet
EndMostRow = wks.Cells(wks.Rows.Count, "G").End(xlUp).Row
dateToCheck = DateValue("12-Mar-2023")
For i = 2 To EndMostRow
If wks.Cells(i, "G").Value = dateToCheck Then
wks.Rows(i).Delete
End If
Next i
End Sub

EndMostRow = wks.Cells(wks.Rows.Count, "G").End(xlUp).Row

If wks.Cells(i, "G").Value = dateToCheck Then
       wks.Rows(i).Delete
End If

8. Remove Rows with Duplicate Value and Shift Up Cells

It is natural that sometimes your dataset may contain duplicate values and you want to delete the duplicates. This method checks the duplicate values in the specified range and then removes the whole rows containing duplicates.

Code:

Sub RemovingDuplicates()
Range("B5:H17").RemoveDuplicates Columns:=2
End Sub

9. Delete Last Row Using VBA

This method will delete the last row from the dataset.

Code:

Sub DeletingLastRow()
Cells(Rows.Count, 4).End(xlUp).EntireRow.Delete
End Sub

10. Remove Table Rows and Shift Up Cells Using VBA

Let’s say, you want to delete a specific row from a table in the Excel worksheet. We have created a table from which we want to delete the 3rd row. For this purpose, we have used the following code.

Code:

Sub RemovingTableRow()
ThisWorkbook.Sheets("Sheet1").ListObjects("Table1").ListRows(3).Delete
End Sub

11. Excel VBA to Delete Active Row in the Worksheet

This code will delete the row containing the active cell. Take your cursor to any cell of the row you want to delete and execute the code to delete the row.

Code:

Sub Deleting_Active_Row()
Rows(ActiveCell.Row).Delete
End Sub

That’s the way to delete row with VBA and shift up in Excel.

How to Delete Columns with Excel VBA

Just like you can delete a row from the dataset, you can also delete a column that you want to be removed. Specify a cell in the code and by the EntireColumn command, you can delete the entire column containing the specified cell in your data range.

Code:

Sub DeletingColumn()
Range("E4").EntireColumn.Delete
End Sub

And if you need to delete any specific column (i.e. column 6), mention it directly in the code.

Code:

Sub DeletingColumn()
Columns(6).Delete
End Sub

How to Delete Specific Cell and Shift Up with Excel VBA

Using VBA code, if you want to delete a specific cell and want the next cells to shift up, use the . Delete the command like the code below.Code:

Sub DeleteCellShiftUp()
Range("F10").Delete
End Sub

But if you just want to clear the contents and keep the cell format that means don’t want to shift the next cells up, use the .ClearContents command. This will leave the cell format just like it was before clearing the cell value.

Code:

Sub ClearingCellValue()
Range("F10").ClearContents
End Sub

Frequently Asked Questions

1. Is it possible to undo the deletion of rows using VBA?

Ans: No, it is not possible to undo the deletion of rows using VBA. Once a row is deleted, it cannot be recovered using VBA.

2. How can I delete a row based on a specific condition using VBA?

Ans: To delete a row based on a specific condition using VBA, you can use an If statement to check the condition and then delete the row if the condition is true.

3. What is the difference between deleting a row and clearing a row in Excel?

Ans: Deleting a row removes the entire row, including any data or formatting it contains, while clearing a row removes only the data from the row, leaving the formatting intact.

Takeaways from This Article

• Deleting rows and shifting up can be a useful way to reorganize your data in Excel.
• “Delete” method of the “Range” object can be used to delete rows and shift up using VBA.
• In case of initiating a loop in VBA, it is safer to start from the last row and then gradually move to the upper ones otherwise row number is changed and desired output is hindered.
• As deleting rows by executing the VBA macro can’t be undone, it is a good practice to keep backup files for necessary data.

Conclusion

There are different ways, situations, and conditions to delete a single row or multiple rows and shift up with Excel VBA. With the use of VBA code, users can delete a row in Excel and shift the remaining rows up to fill the gap. This can be achieved using the Delete method and the Shift argument, as well as the Range object to specify which row to delete. Excel VBA offers many tools for manipulating data, so take the time to explore and experiment with different methods to find the ones that work best for your needs.