Autofilter or SpecialCells-related bug?

[G Lykos]

Greetings! Have run into an apparent glitch, would appreciate any ideas.

Briefly:
1. Selection.AutoFilter Field:=2, Criteria1:="Test" ' isolate rows of
interest using an autofilter
2. Range("B1").Offset(1).Select ' select first data cell below header cell
3. Range(Selection,
Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Select ' select
filtered cells
4. Set Config = Selection ' assign handle to the range collection

Up to here, everything is okay, sort of. If there is at least one visible
row in the filtered list, then Config.count accurately reflects the number
of autofiltered rows (cells, actually), and Config can be used as a
collection for subsequent processing.

HOWEVER, if there are no filtered cells (meaning no data row is visible in
the autofilter), then Config.count is not zero but rather is some large
number (less than total number of rows in data set).

Q1: What is it doing? Is this somehow self-inflicted?
Q2: As a work-around, what are options to identify what should be an empty
collection?

Office 2003 with all updates.

Thanks!
George

 

[Peter T]

Hi George,

No glitch. Manually replicate your scenario, with B3 selected in the hidden
cell, Ctrl-Shift End down-arrow, F5 > Special > Visible cells.
You will end up with all cells in col-B below your filter range selected
with a large row count

For ideas -
Sub test2()
Dim rng As Range
If ActiveSheet.AutoFilterMode Then
' change 1 to column within the Filter range of interest
Set rng = ActiveSheet.AutoFilter.Range.Columns(1)
Set rng = rng(1).Offset(1).Resize(rng.Rows.Count - 1)

On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "no visible cells in filter range"
Else
MsgBox rng.Rows.Count
End If
End If
End Sub

If your filter range is +16k rows, SpecialCells might fail if every other
row is invisible and trying to return more than 8192 non-contiguous ares of
cells. Workaround if necessary.

Regards,
Peter T

 

[Gary Keramidas]

here's what i've done in the past. lest say my range is a5:s8932.
i use these statements to determine the first and last row
fRow = range("A5:s8932").CurrentRegion.Offset(1,
0).SpecialCells(xlCellTypeVisible).Row
lRow = range("A5:s8932").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row

then, if the first row = 5, i know there are no records, because it's the header
row.