How to Delete Multiple Rows in Excel with Condition (3 Ways)

While working in Excel, we sometimes have to delete multiple rows from a data set with a given condition. Today I’ll show you how to delete multiple rows based on a condition in Excel with proper examples and illustrations.

Download Practice Workbook

3 Methods to Delete Multiple Rows in Excel with Condition

Here we’ve got a data set with the Names of some students and their Marks in English in the annual examination of a school called Sunflower Kindergarten.

Our objective is to delete the rows of the students who failed the exam.

That means, to delete the rows of the students who got less than 40 in the examination.

1. Use Sort and Delete Option to Delete Multiple Rows in Excel with Condition

This is the easiest method.

You can use the Sort option in Excel along with the Delete option to delete multiple columns based on a condition.

Step 1:

➤ Create a helper column and enter this formula in the first cell of the column:

• Here C4>=40 is the condition that I want to fulfill. You use your one.

➤ Then drag the Fill Handle to the rest of the cells. It returns “Yes” for the rows that fulfill the condition, and “No” for the others.

Step 2:

➤ Select the whole data set (Including the Column Headers).

➤ Go to the Home > Sort & Filter > Custom Sort tool under the section called Editing.

Step 3:

➤ Click on Custom Sort. A dialogue box called Sort will open.

➤ From the Sort by box, select Helper Column. From the Sort On box, select Cell Values, and from the Order box, select Z to A.

Step 4:

➤ Click on OK.

➤ You will find the rows having “Yes” in the Helper Column piling upwards, and those having “No” piling downwards.

Step 5:

➤ Select all the rows together having “No” in the Helper Column.

➤ Right-click on your mouse and select Delete.

Step 6:

➤ Click on Delete.

➤ The rows having “No” in the Helper Column will be deleted. That means only the rows that satisfy the condition will remain.

Step 7:

Finally, delete the Helper Column.

Read more: How to Delete Multiple Rows in Excel

2 . Run Find and Delete option to Remove Multiple Rows in Excel with Condition

In case you don’t like the above-described method, you can use this method to delete multiple rows in your data set based on a condition.

Step 1:

➤ Create a helper column and enter this formula in the first cell of the column:

• Here C4>=40 is the condition that I want to fulfill. You use your one.

➤ Then drag the Fill Handle to the rest of the cells. It returns “Yes” for the rows that fulfill the condition, and “No” for the others.

Step 2:

➤ Select the Helper column.

➤ Press Ctrl + C on your keyboard to copy the column. The border of the column will be highlighted like this.

Step 3:

➤ Right-click on your mouse.

➤ From the options available, click on Paste Values. It will turn the Helper Column from formula to values.

Step 4:

➤ Again select the Helper Column (Without the Column Header).

➤ Go to the Home > Find & Select > Find option under the section called Editing.

Step 5:

➤ Click on Find. A dialogue box called Find & Replace will open up.

➤ In the Find what box, enter “No”. Put a tick on Match entire cell contents. Then click on Find All.

Step 6:

➤ The cells having “No” in the Helper Column will be shown.

➤ Press CTRL + A on your keyboard. All the cells having “No” in the Helper Column will be selected.

Step 7:

➤ Go to the Home > Delete > Delete Sheet Rows option under the section called Cells.

➤ Click on Delete Sheet Rows.

Step 8:

➤ All the rows having “No” in the Helper Column will be deleted.

.

Step 9:

Finally, delete the Helper Column.

Read more: How to Delete Rows in Excel

3. Run a VBA code to Delete option to Delete Multiple Rows in Excel with Condition

If you don’t like both the above methods, you can run a VBA code and delete multiple rows from your data set based on the condition.

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
If Selection.Cells(i, 2) < 40 Then
Rows(i + 3).EntireRow.Delete
End If
Next i
End Sub

Notes:

• This code produces a Macro called Delete_Rows.
• In the 3rd line of the code, If Selection.Cells(i, 2) < 40 Then, 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 4th line, Rows(i + 3).EntireRow.Delete, 3 is the row number 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:

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

Step 7:

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

Conclusion

Using these methods, you can delete multiple rows in Excel with a given condition. Do you know any other method? Or do you have any questions? Feel free to ask us.

Related Articles

• How to Delete Selected Rows in Excel(8 Approaches)
• How to Delete Every Other Row In Excel (4 Methods)
• How to Delete Selected Rows in Excel(8 Approaches)
• How to Delete Every nth Row in Excel (Easiest 6 Ways)
• How to Use VBA to Delete Empty Rows in Excel
• How to Delete a Row If a Cell is Blank in Excel (4 Methods)