[Excel VBA] How to Show All Rows While Keeping Filter Settings | Guide to ShowAllData

Overview

When working with a filtered worksheet, you may want to temporarily view all records without clearing the filter settings. Excel VBA provides the ShowAllData method, which allows you to toggle the visibility of all rows while maintaining the filter dropdowns and criteria.

In this article, I will explain three practical examples: for the entire worksheet, based on the selected cell, and for specific Tables (ListObject).

Prerequisites

Item	Details
Excel Version	Microsoft 365 / 2016 or later
Filter Status	AutoFilter must already be applied
Macro Location	Standard Module

---

Example 1: Show All Rows in the Entire Worksheet

This macro displays all hidden rows across the entire active sheet, provided a filter is active.

Sub ShowAllRowsOnSheet()

    ' Execute only if a filter exists or criteria are applied
    If ActiveSheet.FilterMode Or ActiveSheet.AutoFilterMode Then
        ActiveSheet.ShowAllData          ' Show all rows (preserves filter arrows)
    Else
        MsgBox "No filter is set on this sheet.", vbInformation
    End If

End Sub

Key Points:

• FilterMode indicates if data is currently being filtered (rows are hidden), while AutoFilterMode indicates if the filter arrows are present.
• After execution, the filter arrows remain, so you don’t have to re-apply the filter setup.

---

Example 2: Automatically Detect Table from Selected Cell

This macro identifies if the active cell belongs to an Excel Table (ListObject) and clears the filters for that specific table only.

Sub ShowAllRowsInSelectedTable()

    Dim tbl As ListObject
    
    ' Determine if the active cell is inside a table
    On Error Resume Next
    Set tbl = ActiveCell.ListObject
    On Error GoTo 0
    
    If tbl Is Nothing Then
        MsgBox "Please select a cell within a table.", vbExclamation
        Exit Sub
    End If
    
    ' Execute only if the table has an AutoFilter
    If Not tbl.AutoFilter Is Nothing Then
        tbl.AutoFilter.ShowAllData
    End If

End Sub

Key Points:

• ActiveCell.ListObject automatically retrieves the table at the cursor’s position.
• This is ideal for sheets containing multiple tables, as it targets only the one you are working on.

---

Example 3: Show All Rows for All Tables on a Sheet

This macro loops through every table on the active sheet and resets their filters to show all data.

Sub ShowAllRowsAllTables()

    Dim ws      As Worksheet
    Dim tblItem As ListObject
    
    Set ws = ActiveSheet
    
    ' Iterate through all tables (ListObjects) in the sheet
    For Each tblItem In ws.ListObjects
        If Not tblItem.AutoFilter Is Nothing Then
            tblItem.AutoFilter.ShowAllData
        End If
    Next tblItem

End Sub

Key Points:

• It traverses the ListObjects collection and clears filters for each table.
• Very efficient for sheets with many tables that need a bulk reset.

---

Frequently Asked Questions

Q: How do I re-apply the filters after showing all data?

A: After using ShowAllData, you can call the AutoFilter method again with your specific criteria to smoothly re-filter the rows.

Q: What if I want to remove the filter arrows entirely?

A: Set ActiveSheet.AutoFilterMode = False. This removes the filter functionality from the header row.

Q: Can I sort the range after showing all rows?

A: Yes. You can call the Sort method after ShowAllData to reorder the records while keeping the filter settings intact.

---

Summary

Using ShowAllData is highly efficient because it allows you to verify all records or perform bulk edits without resetting your filter configuration.

By choosing between “Entire Worksheet,” “Selected Table,” or “Bulk Table Processing,” you can automate your data validation workflow with greater flexibility.