How to Delete Rows in a Range with VBA in Excel?

In this article, I’ll show you how you can delete rows in a range with VBA in Excel. You’ll learn to use VBA to delete every nth row, rows with a specific condition, and rows with a fixed text. The following gif gives an overview of the outcome that we are anticipating from the VBA codes that we will use here.

Click on the image for a better view.

How to Delete Rows in a Range with VBA in Excel (Quick View)

Here, we have the VBA command to delete an entire row. We will customize this command to delete rows of a specific range. In this instance, it deletes the entire row of cell C2 (Row 2, Column 3) of the worksheet.

Cells(2,3).EntireRow.Delete

How to Delete Rows in a Range with VBA in Excel : 3 Easy Ways

In this article, we will discuss 3 ways to delete rows in a range by using VBA codes. Here we’ve got a data set with the Names, Book Types, and Prices of some books from a bookshop called Martin Bookstore. Today, our objective is to delete rows from this data set with VBA code following different conditions.

1. Run a VBA Code to Delete Every nth Row in a Range in Excel

You can run a VBA code to remove every nth row from your data set in Excel.

⧪ Steps:

Firstly, press ALT+F11 on your keyboard.
As a result, the VBA window will open.

Secondly, go to the Insert tab in the VBA window.
From the options available, select Module.

As a result, a new module window called “Module 1” will open.
Insert the following VBA code in the module.

Code:

Sub Delete_Every_nth_Row()
On Error GoTo Message
Dim Count As Integer
Count = 0
n = Int(InputBox("Enter the Value of n: "))
For i = n To Selection.Rows.Count Step n
        Selection.Cells(i - Count, 1).EntireRow.Delete
        Count = Count + 1
Next i
Exit Sub
Message:
    MsgBox ("Please Enter a Valid Integer.")
End Sub

⧪ Note: This code produces a Macro called Delete_Every_nth_Row.

Thereafter, save the workbook as an Excel Macro-Enabled Workbook.

After that, return to your worksheet and select the range of cells from where you want to delete every nth row.
Here, I have selected my data set B4:C13. I want to delete every 3rd row of this data set. So, for the sake of visualizing, I’ve marked every 3rd row with light brown.

Then, press ALT+F8 on your keyboard.
As a result, a dialogue box called Macro will open. Select Delete_Every_nth_Row (The name of the Macro) and click on Run.

Consequently, an Input Box will appear asking you to enter the value of n.
For the sake of this example, I have entered it as 3 (As I want to remove every 3rd row.)

Then, click OK.
Finally, you will find every nth row (3rd in this example) deleted automatically.

2. Insert a VBA Code to Remove Rows with a Specific Condition in a Range in Excel

You can also use a VBA code to remove rows with a specific condition from a data set in Excel. For example, let’s try to delete all the rows with prices greater than $30 from this data set.

⧪ Steps:

To begin with, perform Steps 1-3 from section1 to open the VBA window and insert a new module.
In Step 3, change the VBA code to this:

Code:

Sub Delete_Rows_with_Condition()
On Error GoTo Message
Column_number = Int(InputBox("Enter the Number of Column Where the Condition is Applied: "))
Dim Condition As String
Condition = Int(InputBox("Select Your Condition: " + vbNewLine + "Enter 1 for Greater than a Value." + vbNewLine + "Enter 2 for Greater than or Equal to a Value." + vbNewLine + "Enter 3 for Less than a Value." + vbNewLine + "Enter 4 for Less than or Equal to a Value." + vbNewLine + "Enter 5 for Equal to a Value." + vbNewLine + "Enter 6 for Not Equal to a Value."))
If Condition > 6 Then
    GoTo Message
End If
Value = InputBox("Enter the Value: ")
If Condition <= 4 Then
    Value = Int(Value)
Else
    If VarType(Selection.Cells(1, Column_number)) <> 8 Then
        Value = Int(Value)
    End If
End If
For i = 1 To Selection.Rows.Count
    If Condition = 1 Then
        If Selection.Cells(i, Column_number) > Value Then
            Selection.Cells(i, Column_number).EntireRow.Delete
            i = i - 1
        End If
    ElseIf Condition = 2 Then
        If Selection.Cells(i, Column_number) >= Value Then
            Selection.Cells(i, Column_number).EntireRow.Delete
            i = i - 1
        End If
    ElseIf Condition = 3 Then
        If Selection.Cells(i, Column_number) < Value Then
            Selection.Cells(i, Column_number).EntireRow.Delete
            i = i - 1
        End If
    ElseIf Condition = 4 Then
        If Selection.Cells(i, Column_number) <= Value Then
            Selection.Cells(i, Column_number).EntireRow.Delete
            i = i - 1
        End If
    ElseIf Condition = 5 Then
        If Selection.Cells(i, Column_number) = Value Then
            Selection.Cells(i, Column_number).EntireRow.Delete
            i = i - 1
        End If
    ElseIf Condition = 6 Then
        If Selection.Cells(i, Column_number) <> Value and Selection.Cells(i, Column_number)<>"" Then
            Selection.Cells(i, Column_number).EntireRow.Delete
            i = i - 1
        End If
    End If
Next i
Exit Sub
Message:
    MsgBox "Please Enter a Valid Integer between 1 to 6 and a Logical Value."
End Sub

⧪ Note: This code produces a Macro called Delete_Rows_with_Condition.

After that, save the Workbook as an Excel Macro-Enabled Workbook. Then come back to your worksheet and select your data set to delete rows with conditions.
Here, I’ve selected my data set B4:D13. As will delete the rows with prices greater than $30.00, I have marked them with light brown.

Next, press ALT+F8 to open the Macro box. Then run the Macro Delete_Rows_with_Condition.

As a result, you will get three Input Boxes. The 1st box will ask you to enter the number of the column where the condition lies.

In this example, it is 3 (Price).

The 2nd Input box will ask you to enter a number between 1 to 6 for 6 different types of conditions.

As here our condition is to be greater than a value (Price greater than $30.00), we are entering 1.

The 3rd Input Box will ask you to enter the Value.

As our condition is for the price to be greater than $30.00, we are entering it as 30.

After providing all three inputs, click OK.
As a result, you will get the rows that satisfy your condition (Price greater than 30 in this example) deleted from your data set.

⧪ Additional Example:

You can use the same code to delete all the rows that have the Book Type “Novel”.

In the 1st Input box, enter 2. (As the column Book Type is the 2nd column of the data set.)

After that, enter 5 (As the condition is equal to a value.) in the next input box.

Finally, enter Novel into the last input box.

As a result, you will get all the rows with the Book Type “Novel” deleted.

⧪ Note: You can use the same code to delete rows with blank cells from a data set. Just keep the 3rd Input Box empty.

3. Embed a VBA Code to Delete Rows with a Specific Text Value in a Range in Excel (Partial Match)

You can also use a VBA code to delete rows that contain a specific text value.

For example, let’s try to delete the rows with the text “History” in the book name.

⧪ Steps

At the beginning, follow Steps 1-3 from section 1 to open the VBA window and insert a new module.
In Step 3, change the VBA code to this:

Code:

Sub Delete_Rows_with_Specific_Text()
On Error GoTo Message
Column_number = Int(InputBox("Enter the Number of the Column Where the Text Lies: "))
Text = InputBox("Enter the Specific Text: ")
Dim Count As Integer
For i = 1 To Selection.Rows.Count
    Count = 0
    For j = 1 To Len(Selection.Cells(i, Column_number))
        If Mid(Selection.Cells(i, Column_number), j, Len(Text)) = Text Then
            Count = Count + 1
            Exit For
        End If
    Next j
    If Count > 0 Then
        Selection.Cells(i, Column_number).EntireRow.Delete
        i = i - 1
    End If
Next i
Exit Sub
Message:     
    MsgBox "Please Enter a Valid Integer as the Column Number"
End Sub

⧪ Note:

This code produces a Macro called Delete_Rows_with_Specific_Text.

Thereafter, save the Workbook as an Excel Macro-Enabled Workbook. Then come back to your worksheet and select your data set.
In this case, I’ve selected my data set B4:D13. As I will delete the books with the text “History”, I have marked them in light brown.

Afterward, press ALT+F8 to open the Macro box. Then, run the Macro Delete_Rows_with_Specific_Text.

As a result, you will get two Input Boxes. The 1st box will ask you to enter the number of the column where the condition lies.
In this example, it is 1 (Book Name).

The 2nd Input box will ask you to enter the specific text. In this example, it is History.

[The code is case-sensitive. So “History”, “history”, and “HISTORY” all will do the same here.]

After providing all two inputs, click OK.
Consequently, we will get the rows that contain the specific text (History in this example) deleted automatically.

Thing to Remember

The punchline of all the codes that we used here is:

Selection.Cells(i, Column_number).EntireRow.Delete

Here, Selection.Cells(i, Column_number) denote the cell within the selected range with row number=i and column_number=Column_number.
Selection.Cells(i, Column_number).EntireRow selects the entire row of the cell.
And, Selection.Cells(i, Column_number).EntireRow.Delete deletes the whole row.

Download Practice Workbook

Delete Rows in Range with VBA.xlsm