While working in Excel, we often need to delete rows from our worksheet. If the dataset becomes large, then it’s more convenient to use a Macro rather than manually deleting rows from the worksheet. In this article, we will learn five effective approaches to using a macro to delete rows based on criteria in Excel. So, let’s start this article and explore these methods.
How to Use Macro To Delete Rows Based on Criteria in Excel: 5 Efficient Methods
In this section of the article, we will discuss five efficient methods to use macro to delete rows based on criteria in Excel. Let’s say, we have the Examination Records of Sunflower Kindergarten as our dataset. Our goal here is to delete rows based on criteria using macro.
Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.
1. Using Less Than or Equal to Criteria
In the first method, we will use the less than or equal to criteria to filter our dataset. Let’s say, we want to delete the rows, where a student got marks less than or equal to 40. Let’s use the steps mentioned below to do this.
- Firstly, go to the Developer tab from Ribbon.
- After that, choose the Visual Basic option from the Code group.
Consequently, the Microsoft Visual Basic for Applications window will appear on your worksheet.
- Afterward, go to the Insert tab in the Microsoft Visual Basic for Applications window.
- Then, select the Module option from the drop-down.
Step 02: Write and Save VBA Code
- Firstly, write the following code in the newly created Module.
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
Code Breakdown
- Firstly, we created a sub-procedure named Delete_Rows.
- After that, we initiated two nested For Next loops.
- In the 4th line of the code, we have used Cells(j,2) because 2 is the column number of our data set which holds the criteria (Marks in English).
- And <= 40 is our given condition (Who got marks less than 40).
- Here, we used an IF statement to check whether the condition is true or not for different values of j.
- 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).
- After that, we ended the IF statement.
- Then, we closed both For Next loops.
- Finally, we ended the sub-procedure.
- After writing the code, click on the Save option.
Step 03: Run Code to Delete Rows Based on Criteria
- Following that, apply the keyboard shortcut ALT + F11 to return to the worksheet.
- Afterward, choose the entire dataset.
- Then, use the keyboard shortcut ALT + F8 to open the Macro dialogue box.
- Subsequently, select the Delete_Rows option.
- Finally, click on Run.
Consequently, the rows in which the value in the Marks in English column is less than or equal to 40, will be deleted, as shown in the following image.
2. Deleting Rows Depending on the Criteria of Starting with a Particular Character
Now, we will delete rows in Excel based on a particular starting character using a macro. For example, let’s try to delete the rows that have Student Names starting with “A”. Now, follow the instructions outlined in the following section.
Steps:
- Firstly, use the instructions mentioned in Step 01 of the first method to create a new Module.
- Following that, write the code given below in the newly created Module.
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
Code Breakdown
- Firstly, we created a sub-procedure named Delete_Rows_Starting_with_A.
- After that, we initiated two nested For…Next statements.
- Then, we used an IF statement to check whether the condition is satisfied for different values of j.
- In the condition, we used the Left function to extract the first letter of the cell (j,1).
- Then, we deleted the rows with the names starting with “A”.
- Following that, we ended the IF statement.
- Then, we closed both For Next loops.
- Lastly, we ended the sub-procedure.
- After that, click on the Save option.
- Subsequently, use the keyboard shortcut ALT + F11 to return to the worksheet.
- After that, choose the entire dataset.
- Now, apply the keyboard shortcut ALT + F8 to open the Macro dialogue box.
- Following that, select the Delete_Rows_Starting_with_A option.
- Finally, click on Run.
As a result, the rows that have “A” as their starting character in the Student Name column, will be deleted, as demonstrated in the following picture.
3. Inserting a Macro to Delete Rows Based on a Particular Text
At this stage, we will delete rows in Excel based on a particular text using macro. Let’s say we have the Book Record of Martin Bookstore as our dataset. Our goal is to delete rows that have the word “History” in the Name of the Book.
Steps:
- Firstly, use the procedure discussed in Step 01 of the first method to create a new Module.
- After that, write the following code in the newly created Module.
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 Lower = "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
Code Breakdown
- Firstly, we created a sub-procedure called Delete_Rows_with_History.
- Following that, we introduced a variable named Count as Integer.
- 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 search word in the lowercase form in the code. That means to remove rows with the word “Book”, always use “book” in the code.
- Now, we ended the IF statement.
- After that, we closed both For Next loops.
- Finally, we ended the sub-procedure.
- After writing the code, click on the Save option.
- Following that, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Now, choose the entire dataset.
- After that, apply the keyboard shortcut ALT + F8 to open the Macro dialogue box.
- Subsequently, select the Delete_Rows_with_History option.
- Finally, click on Run.
Consequently, you will have the following output in your worksheet, as shown in the image below.
Read More: Excel VBA to Delete Row If Cell Contains Partial Text
4. Implementing VBA Macro to Delete Rows Based on Multiple Criteria
In this section of the article, we will implement a VBA Macro to delete rows based on multiple criteria. In the previous examples, we used single criteria to delete rows. Here, we used the And function between the two conditions. So, both criteria should be satisfied to delete a particular row. Now, let’s follow the steps mentioned in the following section.
Steps:
- Firstly, use the instructions outlined in step 01 of the first method.
- Following that, write the following code in the newly created Module.
Sub multiple_criteria()
For i = 1 To Selection.Rows.Count
For j = 1 To Selection.Rows.Count
If Left(Selection.Cells(j, 1), 1) = "M" And Selection.Cells(j, 2) < 50 Then
Rows(j + 3).EntireRow.Delete
End If
Next j
Next i
End Sub
Code Breakdown
- Firstly, we created a sub-procedure named multiple_criteria.
- After that, we initiated two nested For…Next loops.
- Then, we used an IF statement to check whether the given conditions are satisfied or not for various values of j. As the And statement is used, both conditions must be satisfied at the same time.
- Now, we deleted the rows with names starting with “M” and marks less than 50.
- Following that, we ended the IF statement.
- Then, we closed both For…Next loops.
- Lastly, we ended the sub-procedure.
- After writing the code, click on the Save option.
- After that, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Then, select the entire dataset.
- Now, use the keyboard shortcut ALT + F8 to open the Macro dialogue box.
- Afterward, choose the multiple_criteria option.
- Finally, click on Run.
Consequently, the rows that have Student Name starting with the letter “M” and Marks in English that are less than 50 will be deleted.
5. Deleting Rows Based on Criteria Defined by User
In the previous examples, we used pre-specified criteria in the VBA code. Now, we will take input from the user and use it as the criteria. Now, let’s follow the steps mentioned below to do this.
Steps:
- Firstly, use the procedure discussed in step 01 of the first method.
- After that, write the following code in the newly created Module.
Sub criteria_by_user()
Dim starting_letter As String
Dim minimun_number As String
starting_letter = InputBox("Enter the Starting Letter:", "Starting Letter")
minimun_number = InputBox("Enter the Minimun Number:", "Minimum Marks")
For i = 1 To Selection.Rows.Count
For j = 1 To Selection.Rows.Count
If Left(Selection.Cells(j, 1), 1) = starting_letter And _
Selection.Cells(j, 2) < minimun_number Then
Rows(j + 3).EntireRow.Delete
End If
Next j
Next i
End Sub
Code Breakdown
- Firstly, we created a sub-procedure named criteria_by_user.
- After that, we introduced two variables named starting_letter and minimun_number.
- Then we used two InputBox functions to get inputs from the user and then assigned these values to the two variables that we declared earlier.
- Following that, we initiated two nested For…Next statement.
- Now, we used an IF…Then statement to check whether the given conditions are satisfied or not for various values of j. As the And function is used, both conditions must be satisfied at the same time.
- Afterward, we deleted the rows with names starting with “M” and marks less than 50.
- Next, we ended the IF statement.
- After that, we closed both For Next loops.
- Lastly, we ended the sub-procedure.
- After writing the code, click on the Save option.
- Following that, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Afterward, select the entire dataset.
- Then, use the keyboard shortcut ALT + F8 to open the Macro dialogue box.
- Now, choose the criteria_by_user option.
- Subsequently, click on Run.
As a result, the Starting Letter dialogue box will appear on your worksheet.
- Now, enter the starting letter for the Student Name column. In this case, we used the letter “M”.
- Then, click OK.
After that, the Minimum Marks dialogue box will open on your worksheet.
- Following that, insert the minimum number for the Marks in English column. Here, we inserted 50.
- Next, click OK.
Subsequently, you will have the following outputs, as demonstrated in the following image.
Read More: How to Delete Row Using Macro If Cell Contains 0 in Excel
How to Delete Blank Rows Using VBA in Excel?
While working in Excel, we often deal with datasets that have a lot of blank rows. Deleting these blank rows manually is a tiresome and time-consuming task. Now, we will use a VBA code to delete blank rows in Excel. Now, let’s use the steps outlined in the following section.
For demonstration, we added some blank rows to the dataset used in the first method.
Steps:
- Firstly, follow the procedure outlined in Step 01 of the first method to create a new Module.
- Following that, write the code given below in the newly created Module.
Sub delete_blank_rows()
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
Code Breakdown
- Firstly, we created a sub-procedure called delete_blank_rows.
- Then, we deleted the blank rows from the selected range.
- Finally, we ended the sub-procedure.
- After writing the code, click on the Save option.
- After that, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Next, select the entire dataset.
- Then, use the keyboard shortcut ALT + F8 to open the Macro dialogue box.
- Afterward, choose the delete_blank_rows option.
- Following that, click on Run.
Consequently, the blank rows will be deleted from your dataset, and you will have the following output.
Read More: Excel VBA: Delete Row If Cell Is Blank
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.
Download Practice Workbook
Conclusion
So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to use the macro to delete rows based on criteria in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.
