Delete Row with VBA and Shift Up Cells in Excel

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to delete rows 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 rows and shift up with Excel VBA.


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.

Activate Visual Basic Editor window with Developer tab in Excel to delete row using vba and shift up cells

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

There are 3 ways to insert 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.

Insert Module in the Excel VBA window

  • 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.

Module window for inserting VBA code in Excel

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.

Note: The code in the Module can be used for any worksheet of the workbook.

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 Rows 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

Specify row no. to delete a single row and shift up

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 follows.VBA code to delete single rowCode:

Sub RemovingSingleRow()
Rows(8).EntireRow.Delete
End Sub
If you Run the code, it will remove the entire 8th row of the Excel sheet.

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

Specify row no. in reverse order to delete multiple rows and shift up

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.

VBA code to delete multiple rowsCode:

Sub RemovingMultipleRows()
Rows(9).EntireRow.Delete
Rows(8).EntireRow.Delete
Rows(7).EntireRow.Delete
End Sub
Note: Mention the row numbers in reverse order because after deleting one row, the remaining ones shift, and the row number changes which will hinder removing the desired rows. Using row numbers in reverse order will help to avoid this drawback.

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.

Alternate syntax to delete multiple rowsCode:

Sub DeletingMultipleRows()
Rows(7 & ":" & 9).Delete
End Sub
This will detect row no. 7th, 8th and 9th and delete them.

Read More: Excel VBA to Delete Entire Row


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 the Selection Range

Selection range would be deleted after running the codeLet’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 to delete entire rows from any selection rangeCode:

Sub DeletingRowInSelection()
Selection.EntireRow.Delete
End Sub
The code will delete the rows of the selection range from the worksheet.

2.2. Removing Rows from Specified Range

Specify cell range to delete the entire rows containing the 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.

VBA code to delete rows from a specified range of cellsCode:

Sub DeleteingRowsFromRange()
Range("C7:C9").EntireRow.Delete
End Sub
Executing the code will delete the entire rows including the cell range specified in code.

Read More: How to Delete Rows in a Range with VBA in Excel?


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

Sequential rows in the selection range which need to be deleted

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.

Initiate For loop in the code to delete sequential rows from selection rangeCode:

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

Code Breakdown

Dim Rng As Range
Dim i As Long
  • These two declare the variable name.
Set Rng = Selection
  • This line sets the “Rng” variable to the currently selected range in the Excel worksheet.
For i = Rng.Rows.Count To 1 Step -2
    Rng.Rows(i).EntireRow.Delete
Next i
  • This block of code initiates a “for” loop that starts from the last row of the selected range and proceeds to the first row, decrementing by 2 rows at a time and deleting the entire row of the current “i” value, which corresponds to every other row within the selected range. The next iteration of the “for” loop continues until all targeted rows have been deleted.

Read More: Excel VBA: Delete Row on Another Sheet


4. Removing Rows and Shifting Up Cells with Blank Cells

Overview of deleting rows containing blank cells in the worksheet

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.

Use SpecialCells property in VBA code to delete blank cellsCode:

Sub RemovingBlankRows()
Set Rng = Range("B5:H17")
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
This code will find the blank cells from the whole specified range and delete the entire rows containing the blank cells. Just Run the code to see the output.

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

5.1. Applying For Loop Nested with If Statement

Overview of removing rows with specific word “Female”

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.

Detect rows with the Specific word “Female” from the 4th column and delete the rowsCode:

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

Code Breakdown

The code here initiates a “For loop” from the first row to the last row of the defined range.

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

This block of “If loop” finds whether the cell of the current row and column 4 matches the word “Female” and immediately deletes the row with this text and then moves to the next row. It continues the iteration throughout the whole dataset till it reaches the last row.


5.2.  Filtering with Condition

Filter the dataset by the word “Female” to delete the visible rows

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

Initiate Autofilter and SpecialCells property of cell in VBA codeCode:

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

Code Breakdown

ActiveCell.AutoFilter Field:=4, Criteria1:="Female"
  • This segment turns on the AutoFilter feature for the currently active cell and filters the data in the 4th column (Field:=4) to show only the rows where the value in the 4th column is “Female” (Criteria1:=”Female”). So keep the first cell of the dataset Active before applying the code.
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete
  • This line deletes all visible rows that match the filter criteria. The Offset(1,0) part tells Excel to start with the second row (to avoid deleting the header row), and the .Rows.SpecialCells(xlCellTypeVisible) part selects only the visible rows. The .Delete method then deletes those rows.

6. Remove the nth Row and Shift Up Cells Automatically

Application of input box for selection range delete an n-th row of the range

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.

Initiate If loop in VBA code to delete 2nd row from selection range

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

Code Explanation

Set Rng = Application.InputBox("Select your range of data (excluding headers):", "Selection of Range", Type:=8)
  • This segment denotes that after executing the code an input box will appear where you have to put the selection range. Type:=8 is used to display an input box that allows the user to select a range of cells on the worksheet, which is then assigned to the Rng variable.

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

Type:=1 displays the “Range” input box
Type:=2 displays the “Number” input box
Type:=4 displays the “Text” input box
Type:=8 displays the “Boolean” input box
Type:=16 displays the “Formula” input box
  • 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

Rows containing the date “12-Mar-2023” from the dataset

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”.

VBA code to delete entire rows based on the date “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

Code Breakdown

EndMostRow = wks.Cells(wks.Rows.Count, "G").End(xlUp).Row
  • This segment sets the value of “EndMostRow” to the row number of the last non-empty cell in column G of the active worksheet.
If wks.Cells(i, "G").Value = dateToCheck Then
       wks.Rows(i).Delete
End If
  • This segment checks whether the cell value in the current row and column “G” is equal to the date value “12-Mar-2023”. If so, the macro deletes the row containing that date.

8. Remove Rows with Duplicate Value and Shift Up Cells

Rows containing duplicate value

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.

Use Remove Duplicates property to delete duplicate rowsCode:

Sub RemovingDuplicates()
Range("B5:H17").RemoveDuplicates Columns:=2
End Sub
This code finds the duplicates based on the 2nd row from the data range(“B5:H17”) and removes the rows containing duplicates.

9. Delete the Last Row Using VBA

Last row in the dataset

This method will delete the last row from the dataset.

Utilize End(xlUp) property to delete last rowCode:

Sub DeletingLastRow()
Cells(Rows.Count, 4).End(xlUp).EntireRow.Delete
End Sub
Here, Cells(Rows.Count, 4).End(xlUp) means the cell in the last row of column 4. The code will delete the last row of column 4 and then shift up. Actually, you can use any column index number if you have a value in each column of the last. The code will delete the last row based on the column index you will enter inside the Cells property.

10. Remove Table Rows and Shift Up Cells Using VBA

Table created from a dataset from which 3rd row would be deleted

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.

Apply ListObjects and ListRows property to remove rows from table

Code:

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

Read More: Excel VBA to Delete Table Row


11. Excel VBA to Delete Active Row in the Worksheet

Active row containing the active cell

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.

VBA code to delete active rowCode:

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

Specify cell range for deleting columns from dataset

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.

Initiate EntireColumn property to delete column from the datasetCode:

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

Visualization of deleting cell and shifting up

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.VBA code to delete specific cellCode:

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

Visualization of clearing content of specific cell

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.

Use ClearContents property to remove cell valueCode:

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.
  • The “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 the 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.

Download Practice Workbook


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.


Related Articles

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.
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo