How to Delete Unwanted Data from a Filtered Table in Excel

In this article, we will show you how to delete specific items from a filtered table in Excel. This is a useful technique when you want to remove some unwanted data from your table without affecting the rest of the data. For example, you may want to delete all the rows that contain a certain value or text in a column.

To delete specific items from a filtered table in Excel, you need to use the AutoFilter feature. AutoFilter allows you to apply filters to your table based on various criteria, such as values, text, dates, colors, icons, etc. You can filter one or more columns in your table and display only the rows that meet your criteria. The filtered rows are visible, while the unfiltered rows are hidden.

To delete the filtered rows, you need to select them and then delete them. However, you cannot simply select the visible rows and press the Delete key, because this will also delete the hidden rows. Instead, you need to use a special method to select only the visible rows and then delete them. There are different ways to do this, depending on whether you want to use the mouse, the keyboard, or VBA code.

Using the Mouse

One way to delete specific items from a filtered table in Excel is to use the mouse. Here are the steps:

1. Select the entire working area of your table. You can do this by clicking on the upper-left corner of the table, or by pressing Ctrl + A.
2. From the Data tab, select the Filter button (under the Sort & Filter group). You will notice small arrows on every cell of the header row. These are meant to help you filter your cells. You can click on any arrow to choose a filter for the corresponding column.
3. In this example, we want to filter out only the rows that contain the item “INF” in column O. So, select the arrow next to the column O header and uncheck the boxes next to all the items, except “INF”. You can simply uncheck Select All to quickly uncheck everything and then just select “INF”. Click OK.
4. You will now see only the rows with item “INF” in column O. To select all the visible rows, click on the row number of the first visible row, and then drag the mouse down to the last visible row. Alternatively, you can press Ctrl + Shift + Down Arrow to select all the visible rows.
5. Right-click on any of the selected rows and select Delete Row from the popup menu. This will delete all the visible rows only. Don’t worry, the rest of your data is safe.
6. To see the rest of the data, simply click on the Filter button from the Data tab again. You should now see all your data, without the rows containing item “INF” in column O.

Using the Keyboard

Another way to delete specific items from a filtered table in Excel is to use the keyboard. Here are the steps:

1. Select the entire working area of your table. You can do this by pressing Ctrl + A.
2. Press Alt + A + T to apply the AutoFilter to your table. You will notice small arrows on every cell of the header row. These are meant to help you filter your cells. You can use the arrow keys to navigate to the column you want to filter, and then press Alt + Down Arrow to open the filter menu.
3. In this example, we want to filter out only the rows that contain the item “INF” in column O. So, use the arrow keys to move to column O, and then press Alt + Down Arrow to open the filter menu. Use the arrow keys and the Spacebar to uncheck the boxes next to all the items, except “INF”. You can simply press Alt + A to quickly uncheck everything and then press Spacebar to select “INF”. Press Enter to confirm the filter.
4. You will now see only the rows with item “INF” in column O. To select all the visible rows, press Ctrl + G to open the Go To dialog box. In the Go to box, type visible and press Enter. This will select all the visible cells in the table.
5. Press Ctrl + – (minus sign) to delete the selected rows. This will delete all the visible rows only. Don’t worry, the rest of your data is safe.
6. To see the rest of the data, simply press Alt + A + T to remove the AutoFilter from your table. You should now see all your data, without the rows containing item “INF” in column O.

Using VBA Code

A third way to delete specific items from a filtered table in Excel is to use VBA code. Here is a sample code that you can use:

Sub DeleteRows()
    Dim oFilter As Object
    With Sheet1.ListObjects("Table1")
        On Error Resume Next
        Set oFilter = .AutoFilter
        On Error GoTo 0
        With .Range
            ' Check if AutoFilter is enabled
            If oFilter Is Nothing Then
                .AutoFilter ' Apply AutoFilter
            Else
                .Parent.AutoFilter.ShowAllData ' Clear existing filters
            End If
            ' Filter column O by item "INF"
            .AutoFilter Field:=15, Criteria1:="INF"
        End With
        Dim rVisRng As Range, rRow As Range
        On Error Resume Next
        ' Get the visible rows in the table
        Set rVisRng = .DataBodyRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rVisRng Is Nothing Then
            ' Loop through the visible rows and delete them
            For Each rRow In rVisRng.Areas
                rRow.EntireRow.Delete
            Next
        End If
        ' Remove AutoFilter if it was not enabled before
        If oFilter Is Nothing Then
            .Range.AutoFilter
        End If
    End With
End Sub

To use this code, you need to open the Visual Basic Editor by pressing Alt + F11. Then, insert a new module by clicking on Insert > Module. Copy and paste the code in the module. You can then run the code by pressing F5, or by clicking on Run > Run Sub/UserForm.

Example

To illustrate how to delete specific items from a filtered table in Excel, let us consider a scenario where we have a table of sales data for a company. The table has 16 columns and 1000 rows, and it looks like this:

Suppose we want to delete all the rows that have the status “INF”, “IWC”, “ONF”, or “OWC”. These are the statuses that indicate incomplete or cancelled orders, and we don’t want to include them in our analysis. We can use any of the methods described above to delete these rows. For example, using the mouse method, we can do the following:

1. Select the entire working area of the table by clicking on the upper-left corner of the table, or by pressing Ctrl + A.
2. From the Data tab, select the Filter button (under the Sort & Filter group). You will notice small arrows on every cell of the header row. These are meant to help you filter your cells. You can click on any arrow to choose a filter for the corresponding column.

3. In this example, we want to filter out only the rows that have the status “INF”, “IWC”, “ONF”, or “OWC”. So, select the arrow next to the column P header and uncheck the boxes next to all the items, except these four. You can simply uncheck Select All to quickly uncheck everything and then just select these four. Click OK.
4. You will now see only the rows with these statuses in column P. To select all the visible rows, click on the row number of the first visible row, and then drag the mouse down to the last visible row. Alternatively, you can press Ctrl + Shift + Down Arrow to select all the visible rows.
5. Right-click on any of the selected rows and select Delete Row from the popup menu. This will delete all the visible rows only. Don’t worry, the rest of your data is safe.
6. To see the rest of the data, simply click on the Filter button from the Data tab again. You should now see all your data, without the rows with these statuses in column P.

Other Approaches

Besides using the mouse, the keyboard, or VBA code, there are some other possible approaches to delete specific items from a filtered table in Excel. Here are some of them:

• Using the Find and Replace feature: You can use the Find and Replace feature to find the items you want to delete and replace them with blank cells. Then, you can sort the table by the column that contains the items and delete the blank rows. For example, to delete the rows with the status “INF”, you can do the following:
  1. Press Ctrl + F to open the Find and Replace dialog box. In the Find what box, type “INF”. In the Replace with box, leave it blank. Click on Options and check the box next to Match entire cell contents. Click on Replace All. This will replace all the cells with “INF” with blank cells.
  2. From the Data tab, select the Sort button (under the Sort & Filter group). In the Sort dialog box, select the column P from the Sort by drop-down list. Make sure the Order is set to A to Z. Click OK. This will sort the table by the status column and bring the blank cells to the top.
  3. Select the blank rows and delete them. You can do this by clicking on the row number of the first blank row, and then dragging the mouse up to the last blank row. Right-click on any of the selected rows and select Delete Row from the popup menu.
• Using the Advanced Filter feature: You can use the Advanced Filter feature to filter the table based on a criteria range that specifies the items you want to delete. Then, you can copy the filtered data to another location and delete the original table. For example, to delete the rows with the status “INF”, you can do the following:
  1. Create a criteria range somewhere on the worksheet. A criteria range is a range of cells that contains the column headers and the criteria for filtering. In this example, we can create a criteria range in cells S1:T2, with the column P header in cell S1 and the item “INF” in cell S2.
  2. Select the entire working area of the table by clicking on the upper-left corner of the table, or by pressing Ctrl + A.
  3. From the Data tab, select the Advanced button (under the Sort & Filter group). In the Advanced Filter dialog box, make sure the Action is set to Copy to another location. In the List range box, select the table range. In the Criteria range box, select the criteria range. In the Copy to box, select a cell where you want to copy the filtered data. For example, you can select cell U1. Click OK. This will copy the filtered data to the new location, without the rows with the status “INF”.
  4. Delete the original table. You can do this by selecting the entire working area of the table and pressing the Delete key. You can also delete the criteria range if you don’t need it anymore.