Excel vba Filtered table BodyRange incorrect SpecialCells

Question

I have a table which I filter with a vba command:

tbl.Range.AutoFilter Field:=11, Criteria1:="=*" & Left(street, 3) & "*", Operator:=xlAnd

It's a *contains* filter, BodyRange (check visually) should count 7 rows, but with this

tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)

I only retrieve less just 4 of them, it's behaving like the BodyRange.SpecialCells capture from the filter only the values beginning with the filter value but not the ones containing it (if my filtered column street are "pilsudzki" "al. pilsudzki" "al. gen pilsudzki" BodyRange will skip all the "al. pilsudzki" etc... and give me only the "pilsudzki" i.e the one beginning with "pils*")

to get around this I must use this trick instead:

Set rnTble = Sheet1.Range("K2:K150000").SpecialCells(xlCellTypeVisible)

For Each cll In rnTble.Cells
Me.ListBox2.AddItem cll.Value
If cll.Value = "" Then Exit For
Next

then and only then I capture all the filtered value containing "*pils*"

Any ideas why is it that way?

question from:https://stackoverflow.com/questions/66054423/excel-vba-filtered-table-bodyrange-incorrect-specialcells

Answer 1

The filtered part is the data divided into several areas. Because they are separated, you need to use areas.

For Each rng In rngTble.Areas
    For Each cll In rng
        Me.ListBox2.AddItem cll.Value
    Next cll
Next rng

No filter

filtered state