How to Delete Rows in a Range with VBA in Excel – 3 Methods

The following GIF provides an overview.

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:

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.

• Go to the Insert tab. 
• Select 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.

• Save the workbook as an Excel 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.

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

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

• Click OK.
• Every nth row (3rd here) is deleted.

Read More: Delete Row with VBA and Shift Up Cells in Excel

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

Use a VBA code to remove rows with a specific condition. Here, 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.

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

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

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

Here, 3 (Price).

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

• The 3rd Box asks you to enter the Value.

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

• Click OK.
• Rows that meet the condition will be deleted.

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

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

• Enter Novel into the last input box.

• Rows with the Book Type “Novel” are deleted.

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

Read More: How to Delete Row If Cell Contains Value Using Macro in Excel

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

Use a VBA code to delete rows that contain a specific text value.

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.

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

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

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

• Click OK.
• Rows containing History are deleted.

Read More: Excel VBA to Delete Row Based on Cell Value

Thing to Remember

The punchline of all codes is:

• 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

Related Articles

• How to Delete Multiple Rows with VBA in Excel
• How to Delete Selected Rows with Excel VBA
• Excel VBA: Delete Row on Another Sheet
• Excel VBA to Delete Table Row