How to Delete Multiple Rows in Excel with Condition?

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.

How to Delete Multiple Rows in Excel with Condition: 3 Methods

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:

Note: 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.

2 . Run the 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:

Note: 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 Find and 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 of 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

Step 4:

• Save the workbook as an Excel Macro-Enabled Workbook.

Step 5:

• Return to your worksheet and 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.

Download Practice Workbook

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 Hidden Rows in Excel?
• How to Delete Filtered Rows in Excel?
• How to Delete Unused Rows in Excel?
• How to Delete Multiple Rows in Excel Using Formula?
• How to Delete Rows in Excel without Affecting Formulas?
• How to Delete Rows in Excel That Go on Forever?
• How to Delete Infinite Rows in Excel?
• How to Remove Rows Containing Identical Transactions in Excel?