# How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)

While working in Excel, you can use a Macro to delete rows based on criteria. Today I’ll show you how you can use a Macro to delete rows based on criteria in Excel with proper illustrations.

## 3 Ways to Use Macro to Delete Rows Based on Criteria in Excel

Here we’ve got a data set with the Names of some students and their Marks and Grades in English of a school called Sunflower Kindergarten. ### 1. Use Macro to Delete Rows Based on Greater than or Equal to Criteria in Excel

You can use a macro to delete rows in Excel based on criteria greater than or equal to a value.

Let’s create a macro that will delete all the rows with the students who got less than 40.

Step 1:

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

Go to the Insert tab in the VBA window.

From the options available, select Module. Step 3:

A new module window called “Module 1” will open.

Insert the following VBA code in the module.

Code:

``````Sub Delete_Rows()
For i = 1 To Selection.Rows.Count
For j = 1 To Selection.Rows.Count
If Selection.Cells(j, 2) < 40 Then
Rows(j + 3).EntireRow.Delete
End If
Next j
Next i
End Sub``````

Notes:

• This code produces a Macro called Delete_Rows.
• In the 4th line of the code, we have used Cells(j,2) because 2 is the column number of my data set which holds the criteria (Marks in English).
• And <40 is my given condition (Who got marks less than 40).
• In the 5th line, we have used Rows(j+3) because 3 is the number of rows in my worksheet just before the first row of the data set (with the Column Headers).
• You change these values according to your data set and condition. Step 4:

Save the workbook as Excel Macro-Enabled Workbook. Step 5: Step 6:

➤ Press ALT+F8 on your keyboard.

➤ A dialogue box called Macro will open. Select Delete_Rows and click on Run. Step 7:

You will find the rows having marks less than 40 deleted automatically. Notes:

• You can also use this code to compare text values.
• For example, to eliminate the students who got A+ in grades, replace the 4th line with If Selection.Cells(j, 3) = “A+” Then
• Here we will use Cells(j,3) because the criterion is now in the 3rd column (Grades). And the output will be: ### 2. Embed a VBA Macro to Delete Rows Depending on Criteria of Starting with a Particular Character

You can use Macro to delete rows in Excel that have text values starting with a particular character.

For example, let’s try to delete the rows that have student names starting with “A”.

The steps are all similar to the steps of method 1.

Just in Step 3, replace the previous VBA code with this code:

Code:

``````Sub Delete_Rows_Starting_with_A()
For i = 1 To Selection.Rows.Count
For j = 1 To Selection.Rows.Count
If Left(Selection.Cells(j, 1), 1) = "A" Then
Rows(j + 3).EntireRow.Delete
End If
Next j
Next i
End Sub``````

Notes:

• This code produces a Macro called Delete_Rows_Starting_with_A.
• In the 4th line of the code, we have used Cells(j,1), because the criterion is in the 1st column (Student Name) of the data set.
• If it is in the second column, use Cells (j,2). Similarly for the 3rd column, use Cells(j,3), and so on.
• And to delete the rows with the names starting with anything other than “A”, change it in the 4th line. Finally, run this Macro on your data set following steps 5, 6, and 7 of method 1.

You will find all the rows having the student names starting with “A” will be deleted automatically. ### 3. Insert a Macro to Delete Rows Based on a Particular Text in Excel

You can also use a Macro to delete rows having a specific text value.

Look at this new data set. We have the Names of some books and the Prices of a bookshop called Martin Bookstore. Let’s try to delete all the rows having the word “History” in the book name.

The steps are all similar to the steps of method 1.

Just in Step 3, replace the previous VBA code with this code:

Code:

``````Sub Delete_Rows_with_History()
Dim Count As Integer
For i = 1 To Selection.Rows.Count
For j = 1 To Selection.Rows.Count
Count = 0
Words = Split(Selection.Cells(j, 1))
For Each k In Words
Lower=LCase(k)
If k = "history" Then
Count = Count + 1
End If
Next k
If Count > 0 Then
Rows(j + 3).EntireRow.Delete
End If
Next j
Next i
End Sub``````

Notes:

• This code produces a Macro called Delete_Rows_with_History.
• In the 6th line of the code, we have used Cells(j,1), because the criterion is in the 1st column (Name of the Book) of the data set.
• If it is in the second column, use Cells (j,2). Similarly for the 3rd column, use Cells(j,3), and so on.
• And to delete the rows with the word anything other than “history”, replace it in the 6th line.
• Also always keep the searching word in the lowercase form in the code. That means to remove rows with the word “Book”, always use “book” in the code. Finally, run this Macro on your data set following steps 5, 6, and 7 of method 1.

And you will find all the rows having “History” or “history”  in the book names will be deleted automatically. ## Conclusion

Using these methods, you can delete rows in Excel based on any criteria using a Macro. Do you have any questions? Feel free to ask us.  