Excel, with its powerful features, often surprises users with its hidden capabilities. Among these, the versatile Filters stand out. Filters allow you to focus on specific data while hiding non-relevant rows, decluttering your view.

But what if you need to delete some of those filtered rows? Maybe you no longer require information about retired employees or other specific criteria.

This article explores how to delete filtered rows in Excel. Whether you prefer manual steps or want to speed things up with VBA code, we’ve got you covered.

Delete Filtered Rows in Excel

How to Delete Filtered Rows in Excel

Let’s consider you have the dataset with the names of some employees with their ages and their designations and you want to delete all rows in which the Designation is Operations.

Step 1: Select the Data Range

Select the Data range where you 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 your preference.

Step 3: Select the range and Click Ok

Step 4: Apply Filter to the Column

Click the arrow located in the column header where you 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: Remove the Filter

You can now remove the filter to show all rows again by clicking the filter icon and then selecting “Select All.”

Step 9: Preview the Result

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

How to Delete Hidden Rows that are not Filtered in Excel

Before applying the technique, ensure to duplicate your unique document in the event of ill-advised working.

Step 1: Navigate to the File Tab and Select Info

Click File in the ribbon, and select Info.

Navigate to the File Tab

Step 2: Select Inspect Document

Click the arrow button in Check for Issues, and select Inspect Document.

Select Inspect Document

Step 3: Put a Tick Mark on Hidden Rows and Columns Choice

Check that the Document Inspector window shows. Drag the parchment bar to the base, and confirm that the Hidden Rows and Columns choice is checked as a matter of course.

Put a Tick Mark on Hidden Rows and Columns Choice

Step 4: Click the Inspect Button on the Base

Check that the Remove All button shows up in Hidden Rows and Columns and that the ‘Number of stowed away lines found’ shows.

Click the Inspect Button on the Base

Step 5: Click Remove All

Confirm that every single secret line and segment containing the information was Go To Specialeliminated.

Step 6: Click the Close Button

Click the Close button to stop the Document Inspector window. Return to the worksheet, check that secret lines are taken out appropriately, and just show columns in the channel are kept.

How to Delete Visible Filter Rows in Excel using Go To Special

Step 1: Select Visible Cells

Select visible cells, then press F5 to load the Go To dialog, and click Special.

Step 2: Select Visible Cells Only and Click Ok

On the Go To Special dialog, mind Visible cells just choose, then, at that point, click OK.

Step 3: Right Click to open the menu and Choose Delete Rows

Verify that visible cells are activated. Right-click to load the menu, and select Delete Row’.

Step 4: Preview Result

Check that apparent cells are eliminated. Grow channel, check that the main secret column is shown.

Deleting Filtered Rows in Excel using VBA

On the current noticeable worksheet, right snap-on sheet name tab to stack the Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window springs up. On the other hand, you can enter the Microsoft Visual Basic for Applications window through Developer ->Visual Basic. You can likewise squeeze the Alt + F11 keys all the while to open it. In the Microsoft Visual Basic for Applications window, click Insert -> Module, and enter underneath code in Module1:

Save the codes, see the screen capture beneath. And afterward quit Microsoft Visual Basic for Applications. Click Developer -> Macros to run Macro. Select ‘RemoveHiddenRows’ and click Run.

Verify that the Remove Hidden Rows message pops up. And ???? hidden rows have been found. All were removed.’ shows on the message. Click OK and check the result. Verify that hidden rows are removed properly.

Conclusion

Deleting filtered rows in Excel is an important skill for data analysts, financial professionals, and anyone working with large datasets. Whether you’re tidying up your spreadsheet, preparing an extract, or conducting comparative analysis, knowing how to remove irrelevant data is essential.

FAQs – Delete Filtered Rows in Excel

Why there is a need to delete rows not shown in a Filter?

Deleting rows not shown in a Filter can help you streamline and optimize your data by removing unnecessary or hidden information. This can improve data analysis, reduce file size, and enhance the clarity of your worksheet.

What are the benefits of using the Document Inspector Method?

The Document Inspector method ensures a thorough examination of your Excel file for hidden rows and columns. It offers a systematic way to identify and remove hidden content, enhancing data privacy and file integrity.

How to recover deleted rows using these methods?

You cannot recover the deleted rows using the above methods. You can only make a backup of your original data to avoid any accidental data loss.

How to delete only specific columns using these methods?

The methods described in the article focus on deleting rows based on the visibility in the filter. If you need to delete specific columns while retaining rows, you may need to modify the provided VBA code or explore alternative approaches.