How to Get the Addresses of Visible Rows from a Filtered Data in Excel using VBA

12:47 AM Unknown 0 Comments

The following function allows you to get the Address of each visible rows from a filtered sets of data in Excel using VBA.

[VBA]

Dim FilteredRows as Variant

Public Function GetFilteredRows(Optional ByVal RowPrefixed As Boolean)

    Dim Rng As Range, rngF As Range, rngVal As Range      'Ranges
    Dim val As Variant       'Range Value
    Dim i As Integer         'Counter
    Dim lRow as long         'Last Row

    Application.ScreenUpdating = False

    Sheets("Raw Data").Select
    lRow = WorksheetFunction.CountA(Range("A:A"))

    'Set the range of all visible cells of the filtered data
    Set rngF = Range("A2", Cells(ActiveSheet.UsedRange.Rows.Count, _
    Range("A2").Column)).SpecialCells(xlCellTypeVisible)
    
    For Each Rng In Range("$A2:$A$" & lRow)
        If Not Intersect(Rng, rngF) Is Nothing Then
            If rngVal Is Nothing Then
                Set rngVal = Rng
            Else
                Set rngVal = Union(rngVal, Rng)
            End If
            If rngVal.Cells.Count = lRow Then Exit For
        End If
    Next Rng
    
    'Resize array variable
    ReDim FilteredRows(0 To Application.CountA(rngVal)) As Variant
    
    For Each val In rngVal
        If RowPrefixed = True Then
            FilteredRows(i) = val.Address
        Else
            FilteredRows(i) = Split(val.Address, "$")(2)
        End If

        Debug.Print val.Address & " - " & Split(val.Address, "$")(2)
        i = i + 1
    Next val
    
    Debug.Print rngVal.Address
    
    Applicaiton.ScreenUpdating = True
End Function

To use the above function, you can assigned the following macro to a button or shape.

Sub SetFilter()
    Call GetFilteredRows(True)
End Sub

And you can see the output in the Immediate window as shown below.