Thanks Norman,

Tom's code works perfectly as a workaround, but it's surprising that such
obvious features don't exist in VBA!
I tried to use
filteredrng.Item(1).Value
filteredrng.Item(2).Value
etc.
but these gave the same values respectively as
entirerng.Item(1).Value
entirerng.Item(2).Value
etc.

I think this is a misleading behaviour of XL! Of course this is intended not
to you but to Microsoft! The easiest solution seems to be reconstructing the
code for using For each!

Regards,
Stefi


€žNorman Jones€� ezt Ã*rta:

Hi Steffi,

How can I refer to cells in filteredrng if I do not use For each?

I am not aware that this is possible.

To navigate the filtered list, you may wish to use code like the following,
which was provided by Tom Ogilvy:

'================
Sub AutoFilterSelectNext()
Dim rng As Range, Rng1 As Range
Dim iCol As Long

iCol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(iCol))

If rng Is Nothing Then Exit Sub

Set rng = Range(ActiveCell.Offset(1, _
iCol - ActiveCell.Column), rng(rng.Count))

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

If Not Rng1 Is Nothing Then
Rng1(1).Select
End If

End Sub
'<<================


--

---
Regards,
Norman



"Stefi" wrote in message
...
Hi All,

I created an autofiltered range consisting of one column with
Set filteredrng = entirerng.SpecialCells(xlVisible)
The statement
For each cell in filteredrng
shows correctly the cells in filteredrng.

How can I refer to cells in filteredrng if I do not use For each?
first cell in filteredrng (Row No of, Value of)
second cell in filteredrng (Row No of, Value of)
etc.

Thanks,
Stefi