Hi Dave,

If you insert the following at 'do what you want' then I believe it
demonstrates what George has been referring to. It appears that the cells
cannot be referenced using VisRng(i). However, as per my previous post, For
Each c In VisRng works.

'Following does not work.
'VisRng.Count is correct.
'Starts at correct cell but loops through
'all cells (including non visible) from
'that point until i VisRng.Count.

For i = 1 To VisRng.Count
MsgBox "Address = " & VisRng(i).Address & Chr(13) _
& "Value = " & VisRng(i).Value
Next i

'This does work and only displays address
'and values of visible cells.
For Each c In VisRng
MsgBox "Address = " & c.Address & Chr(13) _
& "Value = " & c.Value
Next c

Regards,

OssieMac


"Dave Peterson" wrote:

Dim HowManyVisRows as long
dim VisRng as range

With worksheets("somesheetname").autofilter.range
'subtract one for the header.
howmanyvisrows _
= .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1

if howmanyvisrows 0 then
'avoid the header and come down one row
set visrng = .resize(.rows.count-1).offset(1,0) _
.cells.specialcells(xlcelltypevisible)
else
set visrng = nothing
end if
end with

if visrng is nothing then
'warning message???
else
'do what you want
end if

Untested, uncompiled--watch for typos.

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

--

Dave Peterson