When working with large datasets, you may need to delete rows immediately based on the cell values ​​in it (or based on a condition).

Consider the following examples:

1. You have sales data and want to delete all records related to a specific area or product.

2- You want to delete all records whose sales value is less than 100.

3. You want to delete all rows that have an empty cell.

There are several ways to delete this type of data in Excel.

The method you choose to delete rows depends on how the data is structured and the amount or condition of the cell.

In this tutorial, we will teach you different ways to delete rows in Excel based on the amount of cells or a condition.

This tutorial includes:

1- Filter the rows based on the value / condition and then delete it

1- Delete rows containing a specific text

2- Deleting rows based on a numerical condition

2- Sort based on data set and then delete rows

3- Finding and selecting cells based on the number of cells and then deleting rows

4- Delete all rows with an empty cell

5- Filter and delete rows based on cell amount (using VBA)

Filter rows by value / condition and then delete it

One of the fastest ways to delete rows that contain a certain amount or meet certain conditions is to filter them out. Once you have the filtered data, you can delete all of these rows (while the remaining rows remain intact).

Excel filter is quite diverse and you can filter based on many criteria (such as text, numbers, date and color).

Let’s look at two examples where you can filter rows and delete them.

Delete rows containing a specific text

Suppose you have the data set shown in the figure below and you want to delete all rows whose area is Mid-West (in column B).

While in this small data set you can choose to delete these rows manually, most datasets are so large that it will not be easy to delete the row manually.

In this case, you can filter all records in the Mid-West region and then delete all of these rows (while the other rows remain intact).

The following are the steps to delete rows by value (all Mid-West records):

1. Select each cell in the data set whose rows you want to delete.

2. Click on the Data tab.

3- In the “Sort & Filter” group, click on the Filter icon. This option applies filters to all header cells in the data set.

4. Click on the Filter icon in the Region header cell (a downward triangle symbol is at the top right of the cell)

5. Choose other options besides Mid-West (a quick way to do this by clicking Select All and then clicking on Mid-West). This filters the data set and only shows you the Mid-West region record.

6. Select all filtered records.

7. Right-click on any of the selected cells and click “Delete Row”.

8. In the dialog that opens, click OK. At this point, you do not see any records in the dataset.

9. Click on the Data tab and select the Filter icon. This will remove the filter and you will see all the records except the deleted ones.

The above steps first filter the data by cell value (or it can be other conditions such as after / before a date or more or less than one number). You can then simply delete the items.

Some useful shortcuts to speed up the process:

1. To apply or remove the filter, control Control + Shift + L

2- Control + – (hold the control key and press the minus key) to delete the selected cells / rows.

In the example above, I only had four separate areas and I could manually select and remove it from the Filter list (in steps 5 above).

If you have multiple categories / areas, you can type the name at the top of the box (which includes area names) and Excel will show you only those files that match the text entered ( As shown below). When you want to filter text based on it, press the Enter key.

Note that when you delete a row, everything you might have in other cells in that row is lost. One way to achieve this is to make a copy of the data in another worksheet and delete the rows in the copied data. When done, copy it back to the original data location.

Or

You can use the methods shown later in this tutorial.

Delete rows based on a numeric condition

Just as we used the filter method to delete all rows that have Mid-West text, you can also use a number (or date condition).

For example, suppose we have the following data set and we want to delete all rows with less than 200 sales.

Here are the steps to do this:

1. Select each cell in the data.

2. Click on the Data tab.

3- In the “Sort & Filter” group, click on the Filter icon. This applies filters to all header cells in the data set.

4. Click on the Filter icon in the Sales Header cell (a small triangle symbol at the bottom right of the cell)

5. Place the cursor on the Number Filters option. This option shows you all the numbers related to the filter in Excel.

6. Click on the “Less than” option.

7. In the “Custom Autofilter” dialog box that opens, enter the value “200” in the field.

8. Click OK. This will only show filtered files that have a sales value of less than 200.

9- Select all filtered records.

10. Right-click on any of the cells and click Delete Row.

11. Click OK in the dialog that opens. At this point, you do not see any records in the dataset.

12. Click on the Data tab and select the Filter icon. This will remove the filter and you will see all the records except the deleted ones.

There are many filters you can use in Excel – such as less than / greater than, equal to / unequal, between, top 10, above or below average, and so on.

Note: You can also use multiple filters. For example, you can delete all rows that have more than 200 sales but less than 500. In this case, you must use two filter conditions. The Custom Autofilter dialog box allows you to have two filter criteria (as well as OR).

Just like numeric filters, you can filter records by date. For example, if you want to delete all the records of the first quarter, you can do so using the same steps above. When working with date filters, Excel automatically displays the corresponding filters (as shown below).

While filtering is a great way to quickly delete rows based on a value or a condition, it has one drawback – it removes the entire row. For example, in the following case, it deletes all the data on the right of the filtered data set.

What if we just want to delete the records from the data set but we want to keep the remaining data intact?

You can’t do this with filters, but you can do it with sorting.

Sort data sets and then delete rows

Although sorting is another way to delete rows by value, in most cases, it is best to use the filtering method mentioned above.

This sorting method is only recommended when you delete cells by values, not whole rows.

Suppose you have a database as shown below and you want to delete all existing records in the Mid-West region.

Here are the steps to do this using sorting:

1. Select each cell in the data.

2. Click on the Data tab.

3- In the Sort & Filter group, click on the Sort icon.

4. In the Sort dialog box that opens, select Region in Sort by.

5. In the Sort on option, make sure that Cell Values ​​is selected.

6. In the Order option, select A to Z (or Z to A, it does not matter).

7. Click OK. Shows the sorted data set as shown below (sorted by column B).

8. Select all records with the Mid-West region (all row cells, not just the region column).

9. After selecting, right-click and then click Delete. This will open the Delete dialog box.

10. Make sure the “Shift cells up” option is selected.

11. Click OK.

The above steps delete all records where the Mid-West area is located but do not delete the entire row. Therefore, if you have data to the right or left of your data set, it will remain.

In the example above, we have sorted the data by cell value, but you can also use the same steps to sort by numbers, date, cell color, or font color, and so on.

If you want to keep the original data order but delete the records by criteria, you have to have a way to sort the data into the original version. To do this, add a column with the serial number before sorting the data. When you’re done deleting rows / records, simply sort using the extra column you added.

Find and select cells based on cell size and then delete rows

Excel has a Find and Replace feature that lets you find and select cells with a specific value.

After selecting these cells, you can easily delete rows.

Suppose you have the database as shown below and you want to delete all the rows in the Mid-West.

Here are the steps to do this:

1. Select the entire data set.

2. Click on the Home tab.

3. In the Editing group, click on “Find & Select” and then click on Find (you can also use the keyboard shortcut Control + F).

4. In the Find and Replace dialog box, enter the text “Mid-West” in the “Find what:” field.

5. Click Find All. This will instantly show you all the Mid-West text items that Excel was able to find.

6. Use the Control + A keyboard shortcuts to select all the cells that Excel has found. You can also view all selected cells in the dataset.

7. Right-click on any of the selected cells and click Delete. This will open the Delete dialog box.

8. Select the “Entire row” option.

9. Click OK.

The above steps remove all the cells that are the value of the Mid-West region.

Note: Because Replace and Find can control the characters of the text, you can use these when finding data in Excel. For example, if you want to delete all rows in the Mid-West or South-West area, you can use ‘* West’ as text to find in the Find and Replace dialog box. This gives you all the cells where the text ends with the word West.

Delete all rows with an empty cell

If you want to delete all the rows in which there are empty cells, you can easily do this with the built-in function in Excel.

This is the Go-To Special Cells option – lets you quickly select all empty cells. And after selecting all the empty cells, removing these items is very simple.

Suppose you have the data set as shown below and you want to delete all rows that are not worth selling.

Here are the steps to do this:

1. Select the entire data set (in this case A1: D16).

2. Press the F5 key. This will open the “Go To” dialog box (you can also get this dialog box from Home -> Editing -> Find and Select -> Go To).

3. In the “Go To” dialog box, click the Special button. This opens the “Go To Special” dialog box.

4. In the Go To Special dialog box, select “Blanks”.

5. Click OK.

The above steps select all the empty cells in the dataset.

After selecting the empty cells, right-click on each cell and click Delete.

In the Delete dialog box, select “Entire row” and click OK. This will delete all rows with empty cells.

Filter and delete rows by cell value (using VBA)

The last way we want to show you is to include a bit of VBA.

You can use this method if you need to delete rows based on a specific value in a column. You can add VBA code once and add your personal Macro workbook. This way it will be usable in all Excel workbooks.

This code works like the filter method above (except that it goes through all the steps secretly and saves you a few clicks).

Suppose you have the database as shown below and you want to delete all the rows in the Mid-West.

The following VBA code does this.

Sub DeleteRowsWithSpecificText ()

‘Source: https: //trumpexcel.com/delete-rows-based-on-cell-value/

ActiveCell.AutoFilter Field: = 2, Criteria1: = ”Mid-West”

ActiveSheet.AutoFilter.Range.Offset (1, 0) .Rows.SpecialCells (xlCellTypeVisible) .Delete

End Sub

The above code uses the VBA Autofilter method to filter rows according to specified criteria (which is “Mid-West”), then selects and deletes all filtered rows.

Note that we used Offset in the code above to make sure the header row is not deleted.

If your data is in an Excel spreadsheet, the above code will not work. This is because Excel treats the table as a list object. So if you want to delete rows in a table, you have to change the code a bit.

Before deleting the rows, a warning will show you what you see below. This is useful because it allows us to check the filtered row again before deleting.

Remember that when you delete rows using VBA, you can not undo this change. So only do this when you are sure. It is also a good idea to keep a backup of your data if you make a mistake.

If your data is in an Excel spreadsheet, use the following code to delete rows with a specific value in it:

Sub DeleteRowsinTables ()

‘Source: https: //trumpexcel.com/delete-rows-based-on-cell-value/

Dim Tbl As ListObject

Set Tbl = ActiveSheet.ListObjects (1)

ActiveCell.AutoFilter Field: = 2, Criteria1: = ”Mid-West”

Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible) .Delete

End Sub

Because VBA treats the Excel spreadsheet as a list object (not a domain), we had to change the code accordingly.

Where to put VBA code?

This code must be inside a module inside the VB Editor.

Here are some steps you can take to begin the process of preparation for mediation.

1. Open the bookbook where you want to add this code.

2. Use the ALT + F11 keyboard shortcut to open the VBA Editor window.

3. In the VBA Editor window, on the left, there is a “Project Explorer” page (including all workbooks and worksheets). Right-click on any object in the workbook (where you want this code to work), place the cursor on “Insert” and then click on “Module”. This will add the Module object to the workbook and also open the Module code window on the right.

4. Copy and paste the above code in the module window (which appears on the right).

After preparing the code in the VB editor, you can execute the code using any of the following methods (make sure you have each selected cell in the dataset you want to execute this code):

1. Select a line in the code and press the F5 key.

2. Click the Run button on the toolbar in the VB editor.

3- Assign the macro to a button or a shape and click on it in your worksheet to execute it.

4. Add it to the Quick Access toolbar and execute the code with one click.

Note: Because the workbook contains a VBA macro code, you must save it in macro (xlsm) format.