How to Delete a Row using VBA in Excel

You can use VBA code to delete a row or even multiple rows in Excel. This tutorial will cover this in detail. So, let’s get started.

Delete a Single Row with VBA

1. First, specify the row number which you want to delete.
2. Type a dot (.) to get the list of properties and methods.
3. Select the entire row property from there to refer to the entire row.
4. Again, enter a dot and then use the delete method to delete the row.

You can also use other methods to delete a row. These methods are shown in the below example.

In the second line of code, we have used the range object to specify cell A3 in row 3. Then, use the entire row property and the delete method to delete the row.

In the third line of the code, we have used the selection to refer to the selected cell, refer to the entire row, and delete the row.

Sub vba_delete_row()
'all the lines of the below code will delete row 3
Rows(3).EntireRow.Delete
Range("A3").EntireRow.Delete
Selection.EntireRow.Delete
End Sub

Delete Multiple Rows using a VBA Code

You can also delete multiple rows with a VBA code like the single row.

In the above example, we have used the range object to refer to a range.

• In the first code, we have referred to the range A3:A5, which has three continuous rows: 3, 4, and 5.
• In the second line of code, we have referred to non-continued range rows (3 and 5).

Important Note: Delete multiple rows using a sequence from the last row to the first row. If you want to delete rows 3, 5, and 8, delete the 8^th row first, then the 5^th, and then the 3rd. Because when you delete a row, the row below takes its place. If you delete the second row, the third row will take its place. And when you delete the row four after that, you are deleting the row five.

Delete All the Rows in the Sheet

To delete all the rows from a sheet, refer to all the cells and use the delete method.

Enter the cells property and the entire row property, and then use the delete method to delete all the rows from the sheet.

Delete Alternate Rows

Let’s say you want to delete alternate rows (every 2^nd, 5^th, or 10^th Row). To do this, we need to write code using a loop.

Sub vba_delete_row()
Dim iRow As Integer
Dim i As Integer
iRow = Selection.Rows.Count
For i = iRow To 1 Step -2
    Selection.Rows(i).EntireRow.Delete
Next i
End Sub

To understand this code, you need to know two things:

• This code works backward. It deletes the rows from bottom to top.
• It deletes every second row in the selection.

Now let’s understand this code:

In this code, we have used a variable to count the total number of rows to store it.

After that, we used the FOR NEXT loop for every second row in the selection. But here, you need to know that this code starts from the bottom of the selection instead of the start. We have ten rows in the selection, and the code loops from the row 10th to 1st.

In the steps, we have used -2 to tell the code to step upwards in the rows and delete them. If you want to delete every third row, you need to use -3 as the step value.

Delete the Rows Based on the Criteria

To delete a row or multiple rows based on a criteria, you need to use a VBA LOOP and IF STATEMENT.

To understand this code, we need to split it into parts.

1. In the first part, we have declared the variable used in the code to store row count, loop counter, and range address.
2. In the code’s second part, we specified the range A2:B101 in the variable myRange. Then, store the count of the rows in the variable iRow.
3. Next, in the code’s third part, we used the FOR NEXT loop to loop through all the cells in the range.
4. In the end, in the fourth part of the code, we used an IF statement to check if the value in the cell of column B is greater than 40 or not. If it is greater than 40, then delete that row.

And when you run this code, it deletes all rows with values greater than 40.

Sub vba_delete()
Dim iRow As Integer
Dim i As Integer
Dim myRange As Range
Set myRange = Range("A2:B101")
iRow = myRange.Rows.Count
For i = iRow To 1 Step -1
    If myRange.Cells(i, 2).Value > 40 Then
        myRange.Cells(i, 2).EntireRow.Delete
    End If
Next i
End Sub