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