Delete Rows Primarily based on a Cell Worth (or Situation) in Excel [Easy Guide]

When working with giant information units, you could have the necessity to shortly delete rows based mostly on the cell values in it (or based mostly on a situation).

For instance, take into account the next examples:

You might have gross sales rep information and also you wish to delete all of the data for a particular area or product.You wish to delete all of the data the place the sale worth is lower than 100.You wish to delete all rows the place there’s a clean cell.

There are a number of methods to pores and skin this information cat in Excel.

The strategy you select to delete the rows will depend upon how your information is structured and what’s the cell worth or situation based mostly on which you wish to delete these rows.

On this tutorial, I’ll present you a number of methods to delete rows in Excel based mostly on a cell worth or a situation.

Filter Rows based mostly on Worth/Situation and Then Delete it

One of many quickest methods to delete rows that comprise a particular worth or fulfill a given situation is to filter these. Upon getting the filtered information, you’ll be able to delete all these rows (whereas the remaining rows stay intact).

Excel filter is kind of versatile and you’ll filter based mostly on many standards (equivalent to textual content, numbers, dates, and colours)

Let’s see two examples the place you’ll be able to filter the rows and delete them.

Delete Rows that comprise a particular textual content

Suppose you could have a knowledge set as proven under and also you wish to delete all of the rows the place the area is Mid-west (in Column B).

Whereas on this small dataset you’ll be able to select to do delete these rows manually, typically your datasets are going to be enormous the place deleting rows manually gained’t be an choice.

In that case, you’ll be able to filter all of the data the place the area is Mid-West after which delete all these rows (whereas conserving the opposite rows intact).

Under are the steps to delete rows based mostly on the worth (all Mid-West data):

Choose any cell within the information set from which you wish to delete the rowsClick on the Information tab Within the ‘Kind & Filter’ group, click on on the Filter icon. This can apply filters to all of the headers cells within the datasetClick on on the Filter icon within the Area header cell (this can be a small downward-pointing triangle icon on the top-right of the cell)Deselect all the opposite choices besides the Mid-West choice (a fast approach to do that is by clicking on the Choose All choice after which clicking on the Mid-West choice). This can filter the dataset and solely present you data for Mid-West area.Choose all of the filtered recordsRight-click on any of the chosen cells and click on on ‘Delete Row’Within the dialog field that opens, click on on OK. At this level, you will notice no data within the dataset.Click on the Information tab and click on on the Filter icon. This can take away the filter and you will notice all of the data besides the deleted ones.

The above steps first filter the info based mostly on a cell worth (or may be different situation equivalent to after/earlier than a date or larger/lower than a quantity). Upon getting the data, you merely delete these.

Some helpful shortcuts to know to hurry up the method:

Management + Shift + L to use or take away the filterManagement + – (maintain the management key and press the minus key) to delete the chosen cells/rows

Within the above instance, I had solely 4 distinct areas and I may manually choose and deselect it from the Filter record (in steps 5 above).

In case you could have a whole lot of classes/areas, you’ll be able to sort the title within the subject proper above the field (that has these area names), and Excel will present you solely these data that match entered textual content (as proven under). Upon getting the textual content based mostly on which you wish to filter, hit the Enter key.

Word that once you delete a row, something that you could have in different cells in these rows shall be misplaced. One technique to get round that is to create a replica of the info in one other worksheet and delete the rows within the copied information. As soon as achieved, copy it again rather than the unique information.

Or

You should use the strategies proven later on this tutorial (utilizing the Kind technique or the Discover All Technique)

Delete Rows Primarily based on a Numeric Situation

Simply as I used the filter technique to delete all of the rows that comprise the textual content Mid-West, you can too use a quantity situation (or a date situation).

For instance, suppose I’ve the under dataset and I wish to delete all of the rows the place the sale worth is lower than 200.

Under are the steps to do that:

Choose any cell within the dataClick on the Information tabIn the ‘Kind & Filter’ group, click on on the Filter icon. This can apply filters to all of the headers cells within the datasetClick on on the Filter icon within the Gross sales header cell (this can be a small downward-pointing triangle icon on the top-right of the cell)Hover the cursor over the Quantity Filters choice. This can present you all of the quantity associated filter choices in Excel.Click on on the ‘Lower than’ choice.Within the ‘Customized Autofilter’ dialog field that opens, enter the worth ‘200’ within the subjectClick on OK. This can filter and present solely these data the place the gross sales worth is lower than 200Choose all of the filtered recordsRight-click on any of the cells and click on on Delete RowWithin the dialog field that opens, click on on OK. At this level, you will notice no data within the dataset.Click on the Information tab and click on on the Filter icon. This can take away the filter and you will notice all of the data besides the deleted ones.

There are various quantity filters that you need to use in Excel – equivalent to lower than/larger than, equal/doesn’t equal, between, prime 10, above or under common, and many others.

Word: You should use a number of filters as properly. For instance, you’ll be able to delete all of the rows the place the gross sales worth is larger than 200 however lower than 500. On this case, you have to use two filter situations. The Customized Autofilter dialog field permits having two filter standards (AND in addition to OR).

Similar to the quantity filters, you can too filter the data based mostly on the date. For instance, if you wish to take away all of the data of the primary quarter, you are able to do that through the use of the identical steps above. If you’re working with Date filters, Excel routinely exhibits you related filters (as proven under).

Whereas filtering is a good way to shortly delete rows based mostly on a price or a situation, it has one downside – it deletes your complete row. For instance, within the under case, it might delete all the info which is to the correct of the filtered dataset.

What if I solely wish to delete data from the dataset, however wish to preserve the remaining information intact.

You possibly can’t try this with filtering, however you are able to do that with sorting.

Kind the Dataset and Then Delete the Rows

Though sorting is one other technique to delete rows based mostly on worth, however generally, you’re higher off utilizing the filter technique coated above.

This sorting approach is beneficial solely once you wish to delete the cells with the values and never your complete rows.

Suppose you could have a dataset as proven under and also you wish to delete all of the data the place the area is Mid-west.

Under are the steps to do that utilizing sorting:

Choose any cell within the dataClick on the Information tabIn the Kind & Filter group, click on on the Kind icon.Within the Kind dialog field that opens, choose Area within the type by column.Within the Kind on choice, make certain Cell Values is chosenIn Order choice, choose A to Z (or Z to A, doesn’t actually matter).Click on OK. This offers you the sorted information set as proven under (sorted by column B).Choose all of the data with the area Mid-West (all of the cells within the rows, not simply the area column)As soon as chosen, right-click after which click on on Delete. This can open the Delete dialog field.Be sure the ‘Shift cells up’ choice is chosen.Click on OK.

The above steps would delete all of the data the place the area was Mid-West, however it doesn’t delete your complete row. So, you probably have any information on the correct or left of your dataset, it would stay unhurt.

Within the above instance, I’ve sorted the info based mostly on the cell worth, however you can too use the identical steps to type based mostly on numbers, dates, cell shade or font shade, and many others.

Here’s a detailed information on find out how to type information in Excel

In case you wish to preserve the unique information set order however take away the data based mostly on standards, you have to have a technique to type the info again to the unique one. To do that, add a column with serial numbers earlier than sorting the info. When you’re achieved with deleting the rows/data, merely type based mostly utilizing this additional column you added.

Discover and Choose the Cells Primarily based on Cell Worth and Then Delete the Rows

Excel has a Discover and Exchange performance that may be nice once you wish to discover and choose cells with a particular worth.

Upon getting chosen these cells, you’ll be able to simply delete the rows.

Suppose you could have the dataset as proven under and also you wish to delete all of the rows the place the area is Mid-West.

Under are the steps to do that:

Choose your complete datasetClick the Dwelling tabWithin the Enhancing group, click on on the ‘Discover & Choose’ choice after which click on on Discover (you can too use the keyboard shortcut Management + F).Within the Discover and Exchange dialog field, enter the textual content ‘Mid-West’ within the ‘Discover what:’ subject.Click on on Discover All. This can immediately present you all of the cases of the textual content Mid-West that Excel was capable of finding.Use the keyboard shortcut Management + A to pick out all of the cells that Excel discovered. Additionally, you will have the ability to see all the chosen cells within the dataset.Proper-click on any of the chosen cells and click on on Delete. This can open the Delete dialog field.Choose the ‘Whole row’ choiceClick on OK.

The above steps would delete all of the cells the place the area worth is Mid-west.

Word: Since Discover and Exchange can deal with wild card characters, you need to use these when discovering information in Excel. For instance, if you wish to delete all of the rows the place the area is both Mid-West or South-West, you need to use ‘*West‘ because the textual content to search out within the Discover and Exchange dialog field. This offers you all of the cells the place the textual content ends with the phrase West.

Delete All Rows With a Clean Cell

In case you wish to delete all of the rows the place there are clean cells, you’ll be able to simply do that with an inbuilt performance in Excel.

It’s the Go-To Particular Cells choice – which lets you shortly choose all of the clean cells. And after you have chosen all of the clean cells, deleting these is tremendous easy.

Suppose you could have the dataset as proven under and I wish to delete all of the rows the place I don’t have the sale worth.

Under are the steps to do that:

Choose your complete dataset (A1:D16 on this case).Press the F5 key. This can open the ‘Go To’ dialog field (You may as well get this dialog field from Dwelling –> Enhancing –> Discover and Choose –> Go To).Within the ‘Go To’ dialog field, click on on the Particular button. This can open the ‘Go To Particular’ dialog fieldWithin the Go To Particular dialog field, choose ‘Blanks’.Click on OK.

The above steps would choose all of the cells which can be clean within the dataset.

Upon getting the clean cells chosen, right-click on any of the cells and click on on Delete.

Within the Delete dialog field, choose the ‘Whole row’ choice and click on OK. This can delete all rows which have clean cells in it.

In case you’re inquisitive about studying extra about this system, I wrote an in depth tutorial on find out how to delete rows with clean cells. It consists of the ‘Go To Particular’ technique in addition to a VBA technique to delete rows with clean cells.

Filter and Delete Rows Primarily based On Cell Worth (utilizing VBA)

The final technique that I’m going to point out you embody a bit little bit of VBA.

You should use this technique should you typically have to delete rows based mostly on a particular worth in a column. You possibly can add the VBA code as soon as and add it your Private Macro workbook. This manner will probably be out there to be used in all your Excel workbooks.

This code works the identical approach because the Filter technique coated above (besides the truth that this does all of the steps within the backend and prevent some clicks).

Suppose you could have the dataset as proven under and also you wish to delete all of the rows the place the area is Mid-West.

Under is the VBA code that may do that.

Sub DeleteRowsWithSpecificText()
‘Supply:https://trumpexcel.com/delete-rows-based-on-cell-value/
ActiveCell.AutoFilter Area:=2, Criteria1:=”Mid-West”
ActiveSheet.AutoFilter.Vary.Offset(1, zero).Rows.SpecialCells(xlCellTypeVisible).Delete
Finish Sub

The above code makes use of the VBA Autofilter technique to first filter the rows based mostly on the required standards (which is ‘Mid-West’), then choose all of the filtered rows and delete it.

Word that I’ve used Offset within the above code to ensure my header row shouldn’t be deleted.

The above code doesn’t work in case your information is in an Excel Desk. The explanation for that is that Excel considers an Excel Desk as a listing object. So if you wish to delete rows which can be in a Desk, you have to modify the code a bit (coated later on this tutorial).

Earlier than deleting the rows, it would present you a immediate as proven under. I discover this handy because it permits me to double-check the filtered row earlier than deleting.

Keep in mind that once you delete rows utilizing VBA, you’ll be able to’t undo this transformation. So use this solely once you’re certain that this works the best way you need. Additionally, it’s a good suggestion to maintain a backup copy of the info simply in case something goes flawed.

In case your information is in an Excel Desk, use the under code to delete rows with a particular worth in it:

Sub DeleteRowsinTables()
‘Supply:https://trumpexcel.com/delete-rows-based-on-cell-value/
Dim Tbl As ListObject
Set Tbl = ActiveSheet.ListObjects(1)
ActiveCell.AutoFilter Area:=2, Criteria1:=”Mid-West”
Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
Finish Sub

Since VBA considers Excel Desk as a Record object (and never a variety), I needed to change the code accordingly.

The place to Put the VBA code?

This code must be positioned within the VB Editor backend in a module.

Under are the steps that may present you ways to do that:

Open the workbook during which you wish to add this code.Use the keyboard shortcut ALT + F11 to open the VBA Editor window.On this VBA Editor window, on the left, there’s a ‘Challenge Explorer’ pane (which lists all of the workbooks and worksheets objects). Proper-click on any object within the workbook (during which you need this code to work), hover the cursor over ‘Insert’ after which click on on ‘Module’. This can add the Module object to the Workbook and in addition open the Module code window on the rightIn the module window (that may seem on the correct), copy and paste the above code.

Upon getting the code within the VB Editor, you’ll be able to run the code through the use of any of the under strategies (be sure to have the chosen any cell within the dataset on which you wish to run this code):

Choose any line inside the code and hit the F5 key.Click on on the Run button within the toolbar within the VB EditorAssign the macro to a button or a form and run it by clicking on it within the worksheet itselfAdd it to the Fast Entry Toolbar and run the code with a single click on.

You possibly can learn all about find out how to run the macro code in Excel on this article.

Word: For the reason that workbook incorporates a VBA macro code, you have to put it aside within the macro-enabled format (xlsm).

You may additionally like the next Excel tutorials: