CODEWITHSUNDEEP
excelvba
Jakob
Jakob

Reputation: 4854

autofilter not working properly

I'm trying to filter out some cells based on values in a column, but I'm fearing that the first filter action pollutes the rest, although I try to unset the filter by setting filtermode to false.

My select method looks like this:

Function GetRowRange(sheetRange, column, value) As Range
'check for a valid section column
sheetRange.AutoFilterMode = False
sheetRange.UsedRange.AutoFilter Field:=column, Criteria1:=value
Set GetRowRange = sheetRange.UsedRange.SpecialCells(xlCellTypeVisible)
MsgBox ("col:" & column & " val: " & value & " rows:" & GetRowRange.Rows.Count)
sheetRange.AutoFilterMode = False
End Function

And from the msgbox I can see that only the first returns any rows

Upvotes: 0

Views: 1796

Answers (1)

Peter Albert
Peter Albert

Reputation: 17505

This is quite a nasty problem! :-)

The problem is that after applying the Autofilter and .SpecialCells(xlCellTypeVisible), your new Range object is consisting of multiple, combined ranges.

E.g. when filtering this table for B, enter image description here

your range object will have 3 areas, each 1x2 cells. For some strange reason, Rows.Countdoes not take those areas into account, so Selection.Rows.Countis 1 though it should be 3. However, if you loop over the Rows enumeration, it'll work as expected:

lngCount = 0
For Each r In Selection.Rows
    lngCount = lngCount + 1
Next

This will return 3.

So your GetRowRange will return the right range, but the .Rows.Count routine will deliver the wrong result. Use this function instead:

Function RowCount(rngAreas As Range) As Long
    Dim lngCount As Long
    Dim rng As Range
    For Each rng In rngAreas.Areas
        lngCount = lngCount + rng.Rows.Count
    Next
    RowCount = lngCount
End Function

Just a side note: be aware that your GetRowRange will also always return the first header row of your table, so handle this accordingly!

Upvotes: 3

Related Questions