Delete Filtered Rows in Excel

Filters in Excel allow you to temporarily hide rows that don't meet specific criteria, helping we focus only on the data we need. Once filtered, we might want to delete certain rows while keeping others intact especially when cleaning up large datasets or preparing information for analysis.

1. Filtering and Deleting Rows in Excel

Use this method to filter and delete rows, such as removing employees with the "Operations" designation.

Step 1: Select the Data Range

Select the Data range where we want to apply the Filters.

Step 2: Go to the Home Tab and Select Format as Table

Navigate to the Home tab, and then choose the Format as a Table option, selecting the type that suits our preference.

Step 3: Select the range and Click Ok

Select the range and click ok.

Step 4: Apply Filter to the Column

Click the arrow located in the column header where we wish to activate the filter. At this point, all the data in that column will be highlighted.

Step 5: Deselect Select All and check the box next to Operations

Step 6: Preview Result

Check the results after following and performing the steps given here.

Step 7: Delete the Visible Rows

To remove rows, either right-click and choose "Delete Row" from the context menu, or use the keyboard shortcut Ctrl and the minus (-) key.

Step 8: Clear the Filter

• Go back to the Data tab.
• Click on Clear to remove the filter and display all rows.

Step 9: Preview the Result

Check the results after following and performing the steps given here.

2. Deleting Hidden Rows After Filtering

To delete hidden rows (non-filtered data) after applying a filter:

Step 1: Open File and Select Info

• Click the File tab in the ribbon.
• From the menu, select Info.

Step 2: Open Document Inspector

• Click on Check for Issues, located under the Info menu.
• Select Inspect Document from the dropdown.

Step 3: Enable Hidden Rows and Columns Option

• In the Document Inspector window, scroll through the options.
• Ensure the checkbox for Hidden Rows and Columns is selected.

Step 4: Inspect the Document

• Click the Inspect button at the bottom of the Document Inspector window.
• Wait for Excel to scan the document for hidden rows and columns.

Step 5: Remove Hidden Rows

• If hidden rows or columns are detected, the Remove All button will appear.
• Click Remove All to delete all hidden rows and columns from our worksheet.

Step 6: Close the Document Inspector

• Once all hidden rows are removed, click the Close button to exit the Document Inspector window.
• Return to our worksheet and verify that only visible rows related to our filters remain.

3. Deleting Filtered Rows Using Go To Special

To delete only the visible rows after applying a filter, follow these steps using Excel's Go To Special feature:

Step 1: Select Visible Cells

• Highlight the visible rows we want to delete in the filtered dataset.
• Press F5 to open the Go To dialog box.
• Click the Special button in the dialog box.

Step 2: Choose Visible Cells Only

• In the Go To Special dialog box, select the Visible cells only option.
• Click OK to confirm.

Step 3: Delete Rows

• Verify that only the visible cells are selected.
• Right-click within the selected range to open the context menu.
• Choose Delete Rows from the menu.

Step 4: Review the Results

• Verify that the visible rows have been deleted.
• Clear or expand the filter to confirm that only the hidden rows remain in the dataset.

4. Deleting Filtered Rows Using VBA

Automate deletion of filtered rows with VBA:

Step 1: Open the VBA Window

• Right-click on the sheet tab at the bottom of our workbook and select View Code.
• Alternatively, press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Step 2: Insert a Module

• In the VBA editor, click Insert > Module to create a new module.
• Copy and paste the following VBA code into the module:

Sub RemoveHiddenRows()
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    On Error Resume Next
    rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
End Sub

Step 3: Run the Macro

• Close the VBA editor and return to Excel.
• Click Developer > Macros, select RemoveHiddenRows, and click Run.
  (If we don't see the Developer tab, enable it from Excel Options.

Step 4: Confirm Removal

• Once the macro runs, it will delete all visible rows in the filtered dataset.
• Check our worksheet to confirm that only the desired rows remain.

5. VBA - How to Delete Auto-Filtered Rows Except for the Header

Follow these steps to delete rows in Excel with AutoFilter applied, while keeping the header intact:

Step 1: Apply AutoFilter

• Select the data range, including the header row.
• Navigate to the Data tab and click Filter to apply AutoFilter to our dataset.

Step 2: Filter our Data

• Click the dropdown arrow in the column header where we want to filter.
• Select the criteria to filter the rows we want to delete.

Step 3: Select the Filtered Rows

• Once the filter is applied, highlight the visible rows (excluding the header).
• Click the row number of the first visible row.
• Hold the Shift key and click the row number of the last visible row.

Step 4: Delete the Selected Rows

• Right-click the selected row numbers and choose Delete Row from the context menu.
• Alternatively, press Ctrl + - (minus key) to delete the rows quickly.

Step 5: Remove the Filter

• Return to the Data tab and click Filter again to remove the AutoFilter.
• Verify that only the header row and unfiltered data remain.

6. Deleting Filtered Rows Using power Query

Use power Query for advanced data cleaning:

Step 1: Load Data into power Query

• Open our Excel workbook and select the data range or table we want to work with.
• Navigate to the Data tab and click From Table/Range.
• This action will open the power Query Editor.

Step 2: Apply Filters

• In the power Query Editor, locate the column header we want to filter.
• Click the dropdown arrow and select the criteria to filter the rows we wish to keep.

Step 3: Remove Filtered Rows

Once our data is filtered, go to the Home tab in the power Query Editor.

Click Remove Rows in the toolbar.

• Select Remove Blank Rows to eliminate any empty rows.
• Choose Remove Other Rows to delete the filtered-out rows.

Step 4: Close and Load

After completing the changes, click Close and Load to return the modified data to our Excel worksheet.

The cleaned data will now appear in our workbook without the filtered rows.