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.

Data Set to Delete Multiple Rows in Excel with Condition

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:

=IF(C4>=40,"Yes","No")
  • 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.

Helper Column to Delete Multiple Rows in Excel with Condition

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.

Sort Option to Delete Multiple Columns in Excel with Condition

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.

Sort Dialogue Box in Excel

Step 4:

➤ Click on OK.

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

Sorted Data Set to Delete Multiple Rows in Excel with Condition

Step 5:

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

➤ Right-click on your mouse and select Delete.

Delete Multiple Rows in Excel with Condition

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.

Delete Multiple Rows in Excel with Condition

Step 7:

Finally, delete the Helper Column.

Delete Multiple Rows in Excel with Condition

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:

=IF(C4>=40,"Yes","No")
  • 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.

Helper Column to Delete Multiple Rows in Excel with Condition

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.

Copying the Helper Column

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.

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

Find Option in Excel to Delete Multiple Rows

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.

Find & Replace Dialogue Box in Excel

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.

Selecting All the Cells to Delete in Excel

Step 7:

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

➤ Click on Delete Sheet Rows.

Deleting Multiple Rows with Condition in Excel

Step 8:

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

.Multiple Rows with Condition Deleted in Excel

Step 9:

Finally, delete the Helper Column.

Delete Multiple Rows in Excel with Condition

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.

Opening VBA Window in Excel

Step 2:

Go to the Insert tab in the VBA window. 

From the options available, select Module.

Inserting New Module in Excel

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.

VBA Code to Delete Multiple Rows in Excel with Condition

Step 4:

Save the workbook as Excel Macro-Enabled Workbook.

Saving Macro-Enabled Workbook in Excel

Step 5:

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

Selecting Data Set to Delete Multiple Rows in Excel with Condition

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 Multiple Rows in Excel with Condition

Step 7:

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

Delete Multiple Rows in Excel with Condition with VBA


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

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