How to Delete Multiple Rows in Excel with Condition?

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

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.

Read More:


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

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.

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:


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.

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

VBA Code Breakdown

  • 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 an Excel Macro-Enabled Workbook.

Saving Macro-Enabled Workbook in Excel

Step 5:

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


Read More:


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.


Delete Multiple Rows in Excel with Condition: Knowledge Hub


<< Go Back to Delete Rows | Rows in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo