How to Delete Filtered Rows in Excel

Assume we have a considerable rundown in the worksheet (1000 columns or more), and we truly do channel to show just a few cells of the rundown by certain rules. To keep the noticeable cells and eliminate the secret lines to lessen repetitive information and the size of the record, what might we do? On the opposite side, if we need to eliminate the apparent cells in the channel and keep the secret columns, how might we do it? In this article, we will acquaint you with a few strategies to eliminate covered or noticeable columns in the channel.

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

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

How to Delete All Hidden Rows or Columns in Excel Using Inspect Document

We have a rundown of numbers.

Make a channel where the main show lines where values are more prominent than 300.

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.

Step 2: Select Inspect Document

Click the arrow button in Check for Issues, and 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.

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.

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 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.

How to use VBA to delete Filtered Rows in Excel

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.

FAQs

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.