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.


Download Practice Workbook


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.

Data Set to Delete Rows Based on Criteria Using Macro


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.

Opening VBA Window to Delete Rows Based on Criteria Using Macro

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.

Macro to Delete Rows Based on Criteria in Excel

Step 4:

Save the workbook as Excel Macro-Enabled Workbook.

Saving Workbook to Delete Rows Based on Criteria in Excel Using Macro

Step 5:

Return to your worksheet select the data set (Without the Column Headers).

Step 6:

➤ Press ALT+F8 on your keyboard.

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

Running Macro to Delete Rows Based on Criteria in Excel

Step 7:

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

Output of Using Macro to Delete Rows Based on Criteria in Excel

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:

Output of Using Macro to Delete Rows Based on Criteria in Excel

Read more: VBA Macro to Delete Row if Cell Contains Value in Excel


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.

Macro to Delete Rows Based on Criteria

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.

New Data Set to Delete Rows Based on Criteria Using Macro

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.

Macro to Delete Rows Based on Criteria

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.


Related Readings

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