Excel Delete Rows in a Range with VBA (3 Easy Ways)

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.

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

Cells(2,3).EntireRow.Delete

It deletes the entire row of cell C2 (Row 2, Column 3) of the worksheet.


Download Practice Workbook


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

Here we’ve got a data set with the Names, Book Types, and Prices of some books of a bookshop called Martin Bookstore.

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

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 Rows in a Range in Excel

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

Step 1: Opening the VBA Window

Press ALT+F11 on your keyboard. The VBA window will open.

Opening VBA Window to Delete Rows in a Range with VBA in Excel

Step 2: Inserting a New Module

Go to the Insert tab in the VBA window. 

From the options available, select Module.

Step 3: Entering the VBA Code

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.

VBA Code to Delete nth Rows in Excel.

Step 4: Saving the Macro-Enabled Workbook

Save the workbook as Excel Macro-Enabled Workbook.

Saving Workbook to Delete Rows in a Range with VBA in Excel

Step 5: Selecting the Range to Delete Every nth Row

➤ Return to your worksheet and select the range of cells from where you want to delete every nth row.

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

Step 6: Running the Macro

➤ Then press ALT+F8 on your keyboard.

➤ A dialogue box called Macro will open. Select Delete_Every_nth_Row (The name of the Macro) and click on Run.

Running the Macro Delete Rows in a Range with VBA in Excel

Step 7: Entering the Input (The Value of n)

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

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

Step 8: The Final Output!

Then click OK.

You will find every nth row (3rd in this example) deleted automatically.

Read More: How to Delete Every nth Row in Excel (Easiest 6 Ways)


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.

Step 1: The Initial Steps

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

VBA Code to Delete Rows in a Range with Condition with VBA in Excel

Step 2: Selecting the Data Set

Save the Workbook as 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.

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

Step 3: Running the Code

Press ALT+F8 to open the Macro box. Then run the Macro Delete_Rows_with_Condition.

You will get three Input Boxes. The 1st box will ask to enter the number of the column where the condition lies.

In this example, it is 3 (Price).

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

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.

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

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

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

Step 4: The Final Output!

After providing all three inputs, click OK.

You will get the rows that satisfy your condition (Price greater than 30 in this example) deleted from your data set.

Output to Delete Rows in a Range with VBA in Excel

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

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

In the 2nd Input Box, enter 5 (As the condition is equal to a value.)

And in the 3rd Input Box, enter Novel.

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

You will get all the rows with the Book Type “Novel” 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 from a data set. Just keep the 3rd Input Box empty.

Read More: How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)


Similar Readings:


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.

Step 1: The Initial Steps

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

Step 2: Selecting the Data Set

Save the Workbook as Excel-Macro Enabled Workbook. Then come back to your worksheet and select your data set.

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

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

Step 4: Running the Code

Press ALT+F8 to open the Macro box. Then 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 will ask 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”, “HISTORY” all will do the same here.]

Step 5: The Final Output!

After providing all two inputs, click OK.

You will get the rows that contain the specific text (History in this example) deleted automatically.

Output to Delete Rows in a Range with VBA in Excel

Read More: Excel VBA to Delete Rows with Specific Data (9 Examples)


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

Conclusion

Using these methods, you can delete rows within a range with VBA in Excel. Do you have any questions? Feel free to ask us.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo