How to Delete Multiple Rows in Excel Using Formula?

We will be applying 5 methods to remove rows. We’ll use a sample dataset with four columns, Car Name, Company Name, Price, and Max Speed (Mph).

How to Delete Multiple Rows in Excel Using Formula: 5 Methods

Method 1 – Using a Formula with the IF Function to Delete Multiple Rows in Excel

We want to filter cars that have a maximum speed above or equal to 230 miles per hour and don’t want other cars on the screen.

Steps:

Use this formula in cell F5.

=IF(E5>=230,"Yes","No")

Here, the IF function checks the condition in which the value of the E5 cell is greater than or equal to 230 mph or not. It returns Yes when the cell value meets the condition. Otherwise, it returns No.

Hit Enter.

Use the Fill Handle to AutoFill the rest of the cells. Cars that have a maximum speed above or equal to 230 mph are marked as Yes, the others are marked as No.

Select the range B4:F14.

Open the Home tab and select Sort and Filter. You also can use Ctrl + Shift + L.

Click on the arrow in the new column, unmark Yes, then press Enter or click Ok. You will see the information of cars which have a maximum speed less than 230 mph.

Here are the rows that need to be deleted.

Select the range B5 to F12.
Press CTRL + – (minus).
You will see a dialog box that will show you a warning message.
Click OK.

All the selected rows will be deleted.

Remove the Filter from the dataset. You can use Ctrl + Shift + L or deselect Filter from the Data tab.

Method 2 – Applying the AND Function to Delete Multiple Rows

We want to list only cars with a maximum speed of more than 220 mph but that cost less than or equal to $250,000.

Use the following formula in cell F5.

=AND(D5<=250000,E5>220)

The AND function checks the condition given in cells D5 and E5 simultaneously. If any of them doesn’t match the condition, it returns FALSE.

Hit Enter and you will see the output in it.

Use the Fill Handle to AutoFill the rest of the cells. Cars that can run at a maximum speed of more than 220 mph and cost less than or equal to $250,000 are marked TRUE, the others are marked as FALSE.

Select the range B4:F14.
Go to the Data tab and, from Sort and Filter, select Filter or press Ctrl + Shift + L.
Click on the arrow in the header of the new column, unmark TRUE, then press Enter or click OK. You will see the information on cars that don’t have a maximum speed of more than 220 mph and cost more than 250 thousand bucks.

Select the range B5 to F14.
Press Ctrl + –, and you will see a dialog box that will show you a warning message.
Click OK.

This will delete all the selected rows.
To see the hidden rows, remove the Filter from the dataset. You can use Ctrl + Shift+ L or deselect Filter.

Method 3 – Inserting the OR Function to Delete Multiple Rows in Excel

We want to list cars with a maximum speed of more than 220 mph or a price equal to or lower than $230,000.

Use the following formula in cell F5

=OR(D6<=230000,E6>220)

The OR function checks the arguments in cells D6 and E6 simultaneously. If none of them match the condition, it returns FALSE. Otherwise, it returns TRUE.

Hit Enter and you will see the output in it.

Use the Fill Handle to AutoFill the rest of the cells like previous methods.

Select the Range B5:F15.
Apply a Filter like in the previous methods.
Click on arrow on the header of the new column, unmark TRUE, then press Enter or click Ok.

Select the rows that show up and press Ctrl + – (minus).
Click OK.

Remove the Filter from the dataset.

Method 4 – Using the EXACT Function to Delete Multiple Rows Using Formula

We want to list cars manufactured by Porsche.

Use the following formula in cell F5

=EXACT(“Porsche”,C5)

The EXACT function compares the string cell C5 with Porsche. If they match exactly, it returns TRUE, else it returns FALSE.

Hit Enter.

Use the Fill Handle to AutoFill the rest of the cells like previous methods. Cars which are manufactured by Porsche are marked TRUE, while the others are marked as FALSE.

Select Range B4:F14.
Apply a Filter.
Click on the filter arrow for column F, unmark TRUE, then press Enter or click OK.

Select the visible rows with values and press CTRL + -. You will see a dialog box that will show you a warning message.
Click OK.

Remove the Filter from the dataset.

Method 5 – Using Conditions in VBA Code to Delete Multiple Rows

To write code in VBA, open the Developer tab and then select Visual Basic.

This will open a new window for Microsoft Visual Basic for Applications.
Open Insert and select Module.

Insert the following code in the VBA Module.

Sub Del_Row()
For x = 1 To Selection.Rows.Count
For y = 1 To Selection.Rows.Count
If Selection.Cells(y, 3) > 250000 Then
Rows(y + 4).EntireRow.Delete
End If
Next y
Next x
End Sub

We’ve created a Sub procedure Del_Row() x and y are variables to count rows. We used a nested For loop to detect a cell of column no 3 if it is greater than 250000 for that we used the IF statement. Then used the EntireRow property to select that row and deleted the row using the Delete method.
When y = 1, it will select the cell which is in row number 1 and column number 3 of the table you select. In this case, it will take the D5 cell to consider. Then, it will check all the cells in column 3 and delete the rows that contain more than 250,000 dollars.

Save the code and go back to your worksheet.
Select the range B5:E14
Open the Developer tab and elect Macros.

Select the Macro name Del_Row.
Click Run.

This command deletes the rows that had cars with prices more than the listed amount in the code.

Things to Remember

Do not use the Delete button on your keyboard to delete rows after filtering. Follow the steps given in this document.
Select the range according to your code while using VBA. Otherwise, it will show you unexpected results and do the process from the start as you can’t undo it while using VBA.