How to Delete Rows in a Range with VBA in Excel

Observe the GIF:

Click on the image for a better view.

The VBA command will be used to delete an entire row. Customize the command to delete row 2 in the worksheet.

Cells(2,3).EntireRow.Delete

To delete rows from this dataset with VBA:

Sample Data Set

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

Steps:

Press ALT+F11 .
The VBA window will open.

Opening VBA Window

Go to the Insert tab.
Select Module.

Inserting Module

Enter the following VBA code in “Module 1”.

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: The Macro name is Delete_Every_nth_Row.

VBA Code to Delete nth Rows in Excel

Save the workbook as an Excel Macro-Enabled Workbook.

Saving Workbook as Macro Enabled Workbook

Go back to your worksheet and select the range of cells to delete every nth row.
Here, B4:C13 to delete every 3rd row, marked light brown.

Marking Every Third Row

Press ALT+F8 .
In the Macro dialog box, select Delete_Every_nth_Row and click Run.

Running the Macro

An Input Box is displayed asking you to enter the value of n.
Enter 3 (to remove every 3rd row.)

Entering Input

Click OK.

Every nth row (3rd here) is deleted.

Delete Rows in a Range with VBA in Excel

Method 2 – Using a VBA Code to Remove Rows with a Specific Condition in a Range

Remove rows with prices greater than $30.

Steps:

Follow Steps 1-3 in Method1 to open the VBA window and insert a new module.
Use the VBA code:

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: The Macro name is Delete_Rows_with_Condition.

VBA Code

Save the Workbook as an Excel Macro-Enabled Workbook.
Go back to your worksheet and select your dataset to delete rows with conditions.
Here, B4:D13, to delete rows with prices greater than $30.00, marked light brown.

Selecting Data Set to Delete Rows in a Range with VBA in Excel

Press ALT+F8 to open the Macro box. Run the Macro Delete_Rows_with_Condition.

Running VBA Code

You will get three Input Boxes. The 1st box asks you to enter the number of the column with the condition.

Here, 3 (Price).

Entering Input

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

As the condition is greater than a value: $30.00, enter 1.

Entering Input

The 3rd Box asks you to enter the Value.

As the condition is greater than $30.00, enter 30.

Entering 3rd Input into the Message Box

Click OK.

Rows that meet the condition will be deleted.

Output to Delete Rows in a Range with VBA in Excel

Additional Example:

Use the same code to delete all the rows that have the Book Type “Novel”.

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

Entering First Input to Delete Rows in a Range with VBA in Excel

Enter 5 (as the condition is equal to a value) in the next input box.

Inserting 2nd Input

Enter Novel into the last input box.

Entering Final Input to Delete Rows in a Range with VBA in Excel

Rows with the Book Type “Novel” are deleted.

Output to Delete Rows in a Range with VBA in Excel

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

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

Delete the rows with the text “History”.

Steps

Follow Steps 1-3 in Method 1 to open the VBA window and insert a new module.
In Step 3, change the VBA code to:

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:

The Macro name is Delete_Rows_with_Specific_Text.

Save the Workbook as an Excel Macro-Enabled Workbook.
Go back to your worksheet and select your dataset.
Here, B4:D13 to delete the books with the text “History”, marked light brown.

Selecting Data Set to Delete Rows in a Range with VBA in Excel

Press ALT+F8 to open the Macro box. Run the Macro Delete_Rows_with_Specific_Text.

Entering Input to Delete Rows in a Range with VBA in Excel

You will get two Input Boxes. The 1st box asks you to enter the number of the column with the condition.
Here, 1 (Book Name).

Inserting Input to VBA Code

The 2nd Input box asks you to enter the specific text. Here, History.

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

Entering 2nd Input to VBA Code

Click OK.

Rows containing History are deleted.

Output to Delete Rows in a Range with VBA in Excel

Thing to Remember

The punchline of all codes is:

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

Selection.Cells(i, Column_number) is 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.
Selection.Cells(i, Column_number).EntireRow.Delete deletes the whole row.

Download Practice Workbook

Delete Rows in Range with VBA.xlsm