excel - VBA - array of filtered data to populate listbox

Question

Okay so I am filtering a sheet ("Data") by a criteria:

Sub Filter_Offene()
    Sheets("Data").Range("A:R").AutoFilter Field:=18, Criteria1:="WAHR"
End Sub

Then, I want to put the Filtered Table to populate a Listbox My problem here is, that the amount of rows can vary, so I thought i could try and list where the filtered table "ends" by doing this cells.find routine:

Dim lRow As Long
Dim lCol As Long

    lRow = ThisWorkbook.Sheets("Data").Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

lRow = lRow + 1

This unfotunatly also counts "hidden" rows, so in my example it doesnt count 2 but 7.. I've used .Range.SpecialCells(xlCellTypeVisible)before, but It doesn't seem to function with the cells.find above. Does someone have an Idea on how I can count the visible (=filtered) Table, and then put it in a Listbox?

EDIT: I populate the listbox (unfiltered) like this:

Dim lastrow As Long
With Sheets("Data")
    lastrow = .Cells(.Rows.Count, "R").End(xlUp).Row
End With

With Offene_PZ_Form.Offene_PZ
.ColumnCount = 18
.ColumnWidths = "0;80;0;100;100;0;50;50;80;50;0;0;0;0;0;150;150;0"
.List = Sheets("Data").Range("A2:R" & lastrow).Value
End With

But this won't work with filtered Data.

Answer 1

Here is a fun little fact, Excel creates an hidden named range once you start filtering data. If you have continuous data (headers/rows) this would return your range without looking for it. Though since it seem to resemble UsedRange it may still be better to search your last used column and row and create your own Range variable to filter. For this exercise I'll leave it be. Furthermore, as indicated in the comments above, one can loop over Areas of visible cells. I'd recommend a check beforehand just to be safe that there is filtered data other than headers.

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Data")
Dim Area as Range

ws.Cells(1, 1).AutoFilter 18, "WAHR"    
With ws.Range("_FilterDatabase")
    If .SpecialCells(12).Count > .Columns.Count Then
        For Each Area In .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(12).Areas
            Debug.Print Area.Address 'Do something
        Next
    End If
End With

End Sub

The above works if no headers are missing obviously.