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

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

[VBA]

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

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.

?

1 2 3

Sub SetFilter()     Call GetFilteredRows(True) End Sub

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