OssieMac, thought some more about what you said, realized that this is
"deja-vu all over again", and means that there are still basic flaws in the
count and index methods when used with selections involving invisible cells.

Bugs 1 (which you articulated) and 2:
Create a vertical list of header and three data cells. Set an autofilter
that hides the first and third cells. Set a range R using
SpecialCells(xlCellTypeVisible).Select to the three cells: Debug.Print R(1)
yields the second (visible) cell, Debug.Print R(2) yields the third
(invisible) cell [ouch], Debug.Print R(3) yields a fourth cell, outside of
the selection [ouch].

Bug 3 (not mentioned before):
Create a vertical list of header and one data cell. Set a range R using
SpecialCells(xlCellTypeVisible).Select to the cell; Debug.Print R.count =
1. Now set an autofilter that hides the cell, set range R using
SpecialCells(xlCellTypeVisible).Select to the cell; Debug.Print R.count =
16776960 [ouch].

I just checked this in Excel 97. It's really hard to believe that these
same basic bugs are alive and well in Excel 2003 SP3. Wonder if Excel 2007
is the same.

Regards,
George


"OssieMac" wrote in message
...
Hi George,

I did some testing on this. The following did not work because it starts
at
the first visible cell and increments by 1 and includes all addresses
after
the first visible cell. Not what I would have expected:-

iMax = ASN.Count
For i = 1 To iMax
MsgBox ASN(i).Address
Next i

However, this method did work. It only displays the addresses of the
visible
cells. Hopefully using a 'For Each loop' will solve your problem:-

For Each c In ASN
MsgBox c.Address
Next c

Regards,

OssieMac





"G Lykos" wrote:

Help! Have the following snippet, am running into a problem were the
autofilter screens out two records in the data set but the selection set
number of items and the selection set count are different!

Selection.Autofilter Field:=110, Criteria1:="<Test"
Range("A1").CurrentRegion.Sort , Header:=xlYes, _
Key1:=Range("DG1"), Key2:=Range("AZ1")
Range("DG1").Offset(1).Select
Range(Selection,
Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select
Set ASN = Selection
iMax = ASN.Count

The final selection count, and iMax, reflect the correct number of
visible
cells. However, range ASN contains all cells, both visible and
invisible,
determined by stepping through debug.printing ASN(n). Why am I picking
up
invisible cells in the selection set, and how can the selection set
count be
different than the number of selection set elements??

A subsequent work-around was to copy the entire worksheet after
filtering
and sorting and paste it into a scratch worksheet, then set ASN there,
but
I'd much prefer to avoid that gyration. Any ideas?

Windows XP, Excel 2003 SP3.

Thanks!
George