How to filter a list and delete the rest hidden or visible rows in Excel?
When working with large datasets in Excel, it’s common to filter lists to display only the data you need for analysis or reporting. However, once data is filtered, you may also want to remove the rows you no longer need—either by deleting all hidden rows (the ones filtered out), or deleting all visible rows (the ones you’ve chosen to see), so that only your desired information remains. Properly managing filtered data in this way helps simplify worksheets, reduce clutter, and ensures only relevant information is retained. This article introduces several reliable methods for deleting hidden or visible rows in a filtered list in Excel, allowing you to efficiently clean up your data according to your practical needs.
Delete hidden rows in active worksheet with VBA code
Delete visible rows of filtered list with selecting all visible cells
Easily delete hidden or visible rows of filtered list with Kutools for Excel
Delete hidden rows in active worksheet with VBA code
When you filter data in Excel, rows that don’t match your criteria are hidden but still exist in the worksheet. You may sometimes want to quickly remove all of these hidden rows in one step, instead of deleting them one by one manually, which can be time-consuming and error-prone – especially for large datasets. Using a simple VBA macro provides an efficient way to delete all hidden rows from the active worksheet at once, regardless of whether they are hidden by a filter or manually by the user. This is particularly suitable when you want to retain only the currently visible (filtered) data and discard the rest.
Here's how to proceed:
1. First, make sure the worksheet that contains the filtered list is active (selected).
2. Press Alt + F11 together to open the Microsoft Visual Basic for Applications (VBA) editor window.
3. In the VBA editor, click Insert on the top menu, and then choose Module. Copy and paste the following VBA code into the module window:
VBA code: Delete hidden rows
Sub RemoveHiddenRows()
Dim xRow As Range
Dim xRg As Range
Dim xRows As Range
On Error Resume Next
Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
If xRows Is Nothing Then Exit Sub
For Each xRow In xRows.Columns(1).Cells
If xRow.EntireRow.Hidden Then
If xRg Is Nothing Then
Set xRg = xRow
Else
Set xRg = Union(xRg, xRow)
End If
End If
Next
If Not xRg Is Nothing Then
MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
xRg.EntireRow.Delete
Else
MsgBox "No hidden rows found", , "Kutools for Excel"
End If
End Sub4. To run the code, press the F5 key or click the Run button in the toolbar. If your active sheet contains hidden rows, a dialog box will appear, letting you know how many hidden rows have been deleted. Click OK to finish the deletion. See screenshot below:
If there are no hidden rows in the active worksheet, a message will display to inform you accordingly:
Notes and tips:
- This macro removes all hidden rows, regardless of whether they were hidden by filtering or by manual row hiding.
- Check that you have chosen the correct worksheet before running the macro. There’s no Undo after running VBA code – consider making a backup first.
- If you have formulas referencing rows beyond those that will be deleted, be mindful that deleting rows might affect downstream calculations and references.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Delete visible rows of filtered list with selecting all visible cells feature
In some scenarios, you may want to remove only the visible rows after applying a filter—perhaps to leave only the hidden (filtered-out) entries for further processing or review. Excel’s built-in features can help delete all visible rows of a filtered list efficiently, without affecting the hidden rows. This is practical when you need to clear out processed or selected data and retain what remains hidden, such as completed tasks or reviewed records.
Here are the steps to do this:
1. Select the entire range of your filtered list, including both visible and hidden rows.
2. Press F5 (or Ctrl+G) to open the Go To dialog box. Click the Special button at the bottom left corner to open the Go To Special options. See screenshot:
3. In the Go To Special dialog, select Visible cells only, and then click OK. This action highlights only the visible (not filtered or otherwise hidden) rows in your selected range.
4. With the visible rows now selected, right-click anywhere in the highlighted area, and choose Delete Rows from the context menu.
This will immediately remove the visible rows, while the hidden rows remain unaffected under the filtered conditions. It’s a quick way to slim down your worksheet to only those rows that were previously hidden.
Important reminders:
- Be careful when selecting the range at the beginning—ensure it includes all the intended rows, even those that are currently hidden.
- The "Delete Rows" operation cannot be undone if you save and close your file. Consider making a backup before making irreversible changes.
- This method is ideal for occasionally deleting visible rows from a filtered dataset, but can be time-consuming for multiple ranges or frequent tasks.
Easily delete hidden or visible rows of filtered list with Kutools for Excel
While the above manual and VBA methods are commonly used, they may not be flexible or convenient enough in cases where you frequently manage complex or large worksheets. To address these needs more efficiently, the Delete Hidden (Visible) Rows & Columns utility in Kutools for Excel offers a user-friendly way to quickly remove either hidden or visible rows from selected ranges, the current worksheet, or even across all worksheets. This method saves time and lowers the risk of mistakes arising from manual operations, especially useful for users who frequently clean and process large datasets.
Here’s how to use Kutools for Excel for this task:
1. Select the filtered range for which you want to delete either hidden or visible rows.
2. Go to the Kutools tab at the top of the Excel window, then choose Delete > Delete Hidden (Visible) Rows & Columns. See screenshot below for guidance:
3. In the Delete Hidden (Visible) Rows & Columns dialog box that pops up, set your options:
– In the Look in drop-down, keep In selected Range (or select other options if your task requires)
– In the Delete type section, check Rows
– In the Detailed type section, choose either Visible rows or Hidden rows as needed
– Click OK to confirm and execute
4. After execution, a confirmation box will tell you exactly how many rows have been deleted. Click OK to finish the process.
Usage notes and tips:
- Kutools makes it easy to choose a range or apply to the whole worksheet at once, improving batch-processing efficiency.
- This approach reduces errors associated with manual selection, especially in large sheets.
- If you’re dealing with numerous lists or require regular data cleaning, this solution is especially convenient.
- Always confirm your selected range and operation choice before clicking OK.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Delete hidden or visible rows of filtered list with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in