[Excel VBA] How to Get the Current Filter Range (Cell Address)

Overview

When an AutoFilter is applied to a worksheet, retrieving the target cell range (headers + data rows) makes it easier to prevent processing errors outside that range. In this article, I will explain a macro that uses AutoFilter.Range.Address to obtain the filter range and notify the user via a message box.

Prerequisites

Sample Code (VBA)

Sub GetFilterAreaAddress()

    Dim ws      As Worksheet     ' Sheet to be checked
    Dim rngArea As Range         ' Filter range
    
    Set ws = ActiveSheet
    
    ' Check if a filter exists
    If ws.AutoFilterMode = True Then
        
        ' Retrieve the filter range
        Set rngArea = ws.AutoFilter.Range
        
        ' Notify the address
        MsgBox "The current filter range is " & rngArea.Address(False, False) & ".", _
               vbInformation, "Filter Area"
    Else
        MsgBox "No filter is set on this sheet.", _
               vbExclamation, "Filter Area"
    End If

End Sub

Code Explanation

Application Examples

• To select the filter range: Add rngArea.Select.
• To automatically add a filter if missing: Incorporate If Not ws.AutoFilterMode Then ws.Range("A1").CurrentRegion.AutoFilter before processing.
• To use the range as a variable for other tasks: By passing Set rngArea = ws.AutoFilter.Range to subsequent aggregation or copy processes, you can operate safely within the correct boundaries.

Frequently Asked Questions

Q: Is this available for Excel Tables (ListObject) as well? A: Yes. Since Tables internally maintain an AutoFilter, you can retrieve the range using the same property.

Q: Why can’t I retrieve the range after clearing the filter? A: This is because AutoFilterMode becomes False and the filter rows no longer exist. Always check for the existence of a filter beforehand.

Summary

By using AutoFilter.Range.Address, you can easily obtain the cell range currently being filtered. First, run the sample code to confirm that the expected address is displayed. Saving the range into a variable allows for safe operations in subsequent copying, aggregation, or formatting tasks.